First Field of CSV

[BRobinsonS]

I was having trouble with importing a CSV field.
The Database didn't want to pick up the first field.
Thought I wasn't matching the field name in the CSV with Access DB, but I was.

I have worked with CSV import before but it wasn't until I put a comma as the first character that I got the first field to import (all others imported fine).

So apparently the format I must use looks like this:

,MemNum,FirstName,LastName,Street,City,State,Zip,Country,MemType

Is this a flaw in the import function?

[Gaev]

Brian:

I have not had to insert a leading separator character ... do you have (as the first field) an additional field that (perhaps) acts as the Primary Key (where a Unique number is auto incremented by the Database software ?

Post ...

1) the result of dbpGetFieldDefs to see what your Table looks like.

2) details of the dbpImportFromCSV command that you are using to request the import.

3) the first four or five lines of your CSV file.

... to see what the root cause of this is.

[BRobinsonS]

I have not had to insert a leading separator character ... do you have (as the first field) an additional field that (perhaps) acts as the Primary Key (where a Unique number is auto incremented by the database software ?

NO

1) the result of dbpGetFieldDefs to see what your Table looks like.

MemNum String(12)
FirstName String(25)
LastName String(25)
Street String(50)
City String(25)
State String(10)
Zip String(10)
Country String(50) Default="USA"
Telephone String(20)
Email String(35)
Comments Memo
MemType String(5)
DOB DateTime
ActiveStatus Boolean
ActiveDate DateTime
CancelDate DateTime
PickMe Boolean
YearsIn Integer

2) details of the dbpImportFromCSV command that you are using to request the import.

FileExists "[PubDir]Contacts.csv" "[Result]"
IF "[Result]" "=" "True"
MessageBox "[PubTitle]" "Would you like to populate the Address Book with some sample data?" "Yes|No" "[Result]"
IF "[Result]" "=" "1"
.import sample data into contacts table
dbpImportFromCSV "AddrBook" "Contacts" "[PubDir]Contacts.csv" "Delimiter=,;ContainsFieldNames=Yes;MatchFieldNames=Yes"
dbpFirst "AddrBook" "Contacts"
EndIf
EndIf

3) the first four or five lines of your CSV file. (including the , at beginning)

,MemNum,FirstName,LastName,Street,City,State,Zip,Country,MemType
,6530280,Doreen,Capnerhurst,8480 Darlington Cres,Windsor,ON,N8S 4M2,Canada,LF
,6741655,Maude,Dable,3371 Mark Ave,Windsor,ON,N9E 2X2,Canada,LF
,6748522,Harry M,Delisle,877 Rholaine Dr,Windsor,ON,N8S 3Z7,Canada,LF
,1209268,Derek T,Eliott,5850 Cabot Ave,Windsor,ON,N9H 1M1,Canada,LF


By the way,
your app AccessDBU required the same comma at the beginning to import the CSV correctly. I emptied the table and used the Import CSV.

[Gaev]

Brian:

Thank you for the info.

I am at a loss to explain why you need the leading comma but ...

your app AccessDBU required the same comma at the beginning to import the CSV correctly.

... leads me to believe that the root cause lies with the CSV file ... just a stab in the dark here but ...

a) the Help fiel for the dbpImportFromCSV command says

The format of the ASCII file must be compatible with the table it's being imported into.

... you might want to make sure it has the required format ... ANSI and not Unicode or UTF-8 etc.

b) Try using MemNum values that begin with letters (or are all letters) ... perhaps lines starting with numbers is causing a problem.


If all else fails, email me a zip file containing the .mdb and the .csv file ... so I can play with it s'more.

[BRobinsonS]

Interesting thought about encoding.
As best as I can determine, the encoding using EditPad Lite is Windows 1252: Western European. Not sure if this is ascii or other type.
Looked for plain old ascii in the Encoding options but didn't find it.
May be you can tell me?

b) Try using MemNum values that begin with letters (or are all letters) ... perhaps lines starting with numbers is causing a problem.

I tried the MemNum with a letter to begin the number but the same results.

At least I was able to populate the database from the CSV file, although I was curious about why the comma (,) was required.

I am going to try Notepad to load as save the file without the comma to see if that's the problem.

Thanks for the help. Let you know.

[BRobinsonS]

That had to be it. Encoding that is.

Used your app to import a subset of the csv file. Added records fine.

Know a good plain text ascii editor to use for csv files?

[Gaev]

Brian:

Know a good plain text ascii editor to use for csv files?

When Notepad is not able to handle my text editing, I use SciTE ... http://www.scintilla.org/SciTE.html

Others have sworn by Notepad++ ... http://notepad-plus-plus.org/