Source language: Translate to:

Importing csv file into neoDB

Questions about using NeoBook's scripting language

Moderator: Neosoft Support

Importing csv file into neoDB

Postby Wrangler » Wed Feb 28, 2007 2:59 pm

Can't seem to figure this out. I have an existing comma delimited file with 1000 lines. I need to import each field in the file into a field in the db. Got that part, but the description field also contains commas, so when I parse it, it cuts the description up. I tried 2 ways of getting the data into the db:

dbfSetImportExportOptions "," ""
dbfImport "[pubdir]data5.ybn" "[pubdir]tdih.dat"

This only works if I set dbfSetImportExportOptions to use quotes, but the csv file contains no quotes, and I don't want to add them manually.

The slow way:

FileLen "[pubdir]tdih.dat" "[FLen]"
Loop "1" "[FLen]" "[X]"
FileRead "[pubdir]tdih.dat" "[X]" "[Name[X]]"
StrParse "[name[x]]" "," "[item]" "[xx]"
dbfAddRecord "[pubdir]data5.ybn"
SetVar "[data5.day]" "[item1]"
SetVar "[data5.month]" "[item2]"
SetVar "[data5.year]" "[item3]"
SetVar "[data5.event]" "[item4]"
EndLoop

Same here. The problem is getting neobook to know to ignore the commas in [item4]. The way it is, the strparse is cutting it up. The ascii character for the comma is 44. Anyone have any ideas?
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1507
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby Sam Cox » Wed Feb 28, 2007 3:25 pm

Looks like items 1-3 are okay and that only item 4 gets chopped up. If that the case, then you can rebuild item 4 from its pieces as follows:
Code: Select all
FileLen "[pubdir]tdih.dat" "[FLen]"
Loop "1" "[FLen]" "[X]"
  FileRead "[pubdir]tdih.dat" "[X]" "[Name[X]]"
  StrParse "[name[x]]" "," "[item]" "[xx]"
  If "[xx]" ">" "4"
    Loop "5" "[xx]" "[n]"
      SetVar "[item4]" "[item4],[item[n]]"
    EndLoop
  Endif
  dbfAddRecord "[pubdir]data5.ybn"
  SetVar "[data5.day]" "[item1]"
  SetVar "[data5.month]" "[item2]"
  SetVar "[data5.year]" "[item3]"
  SetVar "[data5.event]" "[item4]"
EndLoop

After parsing the record in CSV format, we concatenate pieces 4 and up (if necessary) putting back the commas as we go. The rest of your code is unchanged.

-- Sam,

P.S., Why are you reading each line of the file into a distinct variable? Unless you need to use them elsewhere in the program, you could just overwrite [name] each time without using/creating [name1], [name2], and so on.
User avatar
Sam Cox
 
Posts: 768
Joined: Fri Apr 01, 2005 7:30 am
Location: Loveland CO USA

Postby Wrangler » Wed Feb 28, 2007 3:30 pm

Thanks, Sam. I'll give it a try. And you're right. By reusing [name], I would conserve a lot of horsepower. :oops:

Added Later: Works like a champ. Appreciate the help.
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1507
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby Neosoft Support » Thu Mar 01, 2007 11:04 am

It sounds like your source file isn't a valid CSV file. Any field that contains a comma must be surrounded by quotes. For example:

Joe,Smith,"123 Any Street, Apt 13",Reno,NV,12345
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5605
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Postby Wrangler » Thu Mar 01, 2007 11:21 am

Actually, it started out as a mysql database dump. I removed any garbage I didn't need. The descriptions weren't surrounded in quotes, and I didn't want to do it manually. Sam's little snippet worked out well.
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1507
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby Gaev » Thu Mar 01, 2007 12:07 pm

Dave:

There have been a few occasions in the past where I have had to resort to the kind of Loop/EndLoop scripting like the one posted by Sam ... where I had wished NeoBook had functionality similar to that offered by JavaScript and php ... that enabled one to Join specified elements (items) of an array ... a kind of Reverse-StrParse function ... e.g.
Code: Select all
JoinArrayItems "array name" "starting array item" "ending array item" "join string" "variable"
... so one could code something like ...
Code: Select all
JoinArrayItems "myArray" "4" "9999" "," "[myCSVText]"

When you have hundreds of records with tens of array items, one Delphi routine would save a lot of time over a NeoBook Loop/EndLoop ... perhaps an item for the famous "Wish List" ?
User avatar
Gaev
 
Posts: 3738
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby HPW » Thu Mar 01, 2007 12:47 pm

> that enabled one to Join specified elements (items) of an array

Maybe hpwListParse/hpwListJoin of hpwControl would help.
Hans-Peter
User avatar
HPW
 
Posts: 2521
Joined: Fri Apr 01, 2005 11:24 pm
Location: Germany

Postby Neosoft Support » Fri Mar 02, 2007 12:12 pm

Something like JoinArrayItems is an interesting idea. In PHP you would need to use two functions (array_slice and join) to accomplish the same thing. In Delphi you would actually need to do something similar to Sam's script to accomplish this.

If [Item4] always contains the same number of commas, then you could speed up the import like this:

Code: Select all
FileLen "[pubdir]tdih.dat" "[FLen]"
Loop "1" "[FLen]" "[X]"
  FileRead "[pubdir]tdih.dat" "[X]" "[Name[X]]"
  StrParse "[name[x]]" "," "[item]" "[xx]"
  dbfAddRecord "[pubdir]data5.ybn"
  SetVar "[data5.day]" "[item1]"
  SetVar "[data5.month]" "[item2]"
  SetVar "[data5.year]" "[item3]"
  SetVar "[data5.event]" "[item4][item5][item6][item7][item8]"
EndLoop


If the size of [Item4] varies, then you might need to add a call to DeleteArray to the loop to clear the array between iterations. Array elements that don't exist will be ignored by the last SetVar action.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5605
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Postby Gaev » Fri Mar 02, 2007 3:04 pm

In a special case like this, another way to speed up the process would be to remove the first three items from the original string ...
Code: Select all
FileLen "[pubdir]tdih.dat" "[FLen]"
Loop "1" "[FLen]" "[thisLineNumber]"
  FileRead "[pubdir]tdih.dat" "[thisLineNumber]" "[thisLineData]"
  StrParse "[thisLineData]" "," "[item]" "[xx]"
  SetVar "[Items_1_to_3]" "[item1,[item2],[item3],"
  StrLen "[Items_1_to_3]" "[Items_1_to_3_Len]"
  StrDel "[thisLineData]" "1" "[Items_1_to_3_Len]" "[new_item4]"
  dbfAddRecord "[pubdir]data5.ybn"
  SetVar "[data5.day]" "[item1]"
  SetVar "[data5.month]" "[item2]"
  SetVar "[data5.year]" "[item3]"
  SetVar "[data5.event]" "[new_item4]"
EndLoop

... does not matter whether [Item4] always contains the same number of commas or not.
User avatar
Gaev
 
Posts: 3738
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby Neosoft Support » Mon Mar 05, 2007 12:54 pm

In a special case like this, another way to speed up the process would be to remove the first three items from the original string...


Even better!

Thanks Gaev.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5605
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA


Return to NeoBook Action Commands

Who is online

Users browsing this forum: No registered users and 1 guest