Source language: Translate to:

Import Delimiter=,

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Import Delimiter=,

Postby BRobinsonS » Sat Nov 12, 2011 12:11 pm

I have been working with Import function.

The Delimiter seems to only allow for comma in CSV files to separate the fields.

Most of the CSV files I have seen use "field1","field2","etc".

Can "," be used for import?

The problem I have is a memo field where users can use commas (,) to breaking up the words/sentences in the memo field.

I would like to use "," to import so I don't have to do a lot of editing.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Postby Gaev » Sat Nov 12, 2011 6:46 pm

Brian Robinson:
The Delimiter seems to only allow for comma in CSV files to separate the fields.
... reproduced from the Help file ...
dbpImportFromCSV "database id" "table" "filename" "properties"

database id
The name assigned to the database containing the table you want to import into.

table
The name of the table that will receive the imported data.

filename
The name and location of the external file containing the data to be imported.

properties
This is a compound parameter and can contain any combination of the following items:

Delimiter=char
The delimiter character used to separate field data in the file. You must specify the correct delimiter or the file will not import properly. The most common delimiter character is a comma (,).


IncludesFieldNames=Yes/No
Yes = The first line in the file contains field names. No = the first line in the file contains data.

MatchFieldNames=Yes/No
Yes = if possible match the file's field names to the table's field names when importing data. No = ignore the file's field names and import field data into the table in the order it appears.

Separate multiple items in a compound variable with semicolons (;).

... so it looks like you can choose something other than comma.
Most of the CSV files I have seen use "field1","field2","etc".
Can "," be used for import?
The problem I have is a memo field where users can use commas (,) to breaking up the words/sentences in the memo field.
I would like to use "," to import so I don't have to do a lot of editing.
... haven't tried NeoDBPro's facilities ... but some parsers do not handle the wrapping quotation ( " ) marks ... which makes it difficult when field contents contain commas.

Try using dbpImportFromCSV with a delimiter like ~ or even $$ or :::
User avatar
Gaev
 
Posts: 3717
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby BRobinsonS » Sun Nov 13, 2011 6:45 am

Guess I can try another delimiter by search and replacing all "" by something else.

NeoBookDBPro export function allows the optional ",".

The file I want to work with is received from downloading from a website is in the format:

"field1","field2","field3","etc"

I want to use my Neobook app to report the results.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Postby Wrangler » Sun Nov 13, 2011 8:08 am

When importing a comma seperated file, any field data that contains commas must be surrounded in quotes. Neodbpro looks for fields like this and imports them correctly. Otherwise, it will take a comma in a memo or other field as the end of the field, since commas are the delimiter.

Post the code you are trying with and we can have a look.
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: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby BRobinsonS » Sun Nov 13, 2011 12:54 pm

Sample of CSV file to import for voting for business award
"id","bizname","owner_name","nominated_by","nominator_email","reason"
"25","sunset ice cream and gifts","Cherie Dillen","Sandra Lunn","sandrazachmeier@yahoo.de","34 donations to the community, great effort to make customers happy, "
"26","Extreme Contracting","Mike Martin","Mike Labonte","mlabonte@cogeco.ca","Mike Martin, Extreme Contracting, made our bathroom renovation a reality. Every Extreme Team Member showed respect and worked very hard to gain customer satisfaction. The Extreme Team transformed a standard 1980 bath into a serene, peaceful spa paradise. The renovated bathroom has become the showcase room in our home! Mike Martin, Extreme Contracting, was an excellent choice as our definitive source for our bathroom renovation. "


Can I leave "" and it should import correctly or, must I edit the CSV always?

dbpImportFromCSV "[DatabaseName]" "[TableName]" "[CSVFile]" "Delimiter=,;ContainsFieldNames=Yes;MatchFieldNames=No"
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Postby Wrangler » Sun Nov 13, 2011 12:57 pm

You can leave the "" in the csv file. Give it a try. You can always empty the table and start again.
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: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby BRobinsonS » Sun Nov 13, 2011 2:29 pm

I have been trying to import leaving "". First didn't work then..
Got playing around with different import combinations:
id,"Second Field",third,fourth >>> works
id,"Second, Field",third,fourth,"fifth " >>> works
Tried: small file with all "" around fields >> works, but not larger file.

I guess I need to look at bigger file and see why it doesn't want to import !

Too bad there is not an error to indicate import failed.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Solved

Postby BRobinsonS » Sun Nov 13, 2011 5:24 pm

Alright this is embarrassing, the csv file I have been trying to import did not have the invisible CR/LF at the end of each line.

I went through all the lines hit Enter/Return key and Voila it imported.

I got suspicious when my smaller sample files would import.
Of course, they had carriage returns.

Thanks for the feedback.
Thanks for NeoBook, NeoBookDBPro.

Learned alot about both.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Postby Wrangler » Sun Nov 13, 2011 5:31 pm

That's how computers work. Garbage in, garbage out. :) Glad you got it figgered out. I guess the tip here is to view the csv file without wordwrap. If it's all on one line, it lacks [#13][#10] (CR/LF) at the end of each line and won't import.
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: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby BRobinsonS » Sun Nov 13, 2011 5:49 pm

You are fast.

Since I am maintaining the website responsible for exporting the CSV file, I am going to look at the PHP code which generates the file.

Looked at your website. Very good. Is it bringing you sucess?

Thanks for your input.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Postby Wrangler » Sun Nov 13, 2011 5:55 pm

Probably needs a /n somewhere. The append feature in php doesn't append to a new line. A /n at the end of the filewrite data should fix it.

Thanks for the compliment. Doesn't make a living. But serious beer money. :o
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: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby Gaev » Sun Nov 13, 2011 9:06 pm

Brian Robinson:
Since I am maintaining the website responsible for exporting the CSV file, I am going to look at the PHP code which generates the file.
... alternately, you could use NeoBook's StrReplace command to replace all the [#13] characters with [#13][#10] ... doing a FileRead before and a FiileWrite after the replacements.
User avatar
Gaev
 
Posts: 3717
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest

cron