," at end of file

[Sash]

I have a comma delimited file that has a ," after the last record on each
row. When I import, I naturally get unparsable record error. Any ideas to
work around this?

 

[Jeff Boyce]

Have you tried not importing, but linking to it instead?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Sash]

We receive a flat file every day from numerous customers. I need to load
them into my database in order to format the data to load into another
system. I have one program that exports the data for each client, so I'm
importing them using specs that identify the fields as necessary to run
through my program. Hope this makes sense.

I was trying to make it as seamless as possible for the user.

 

[gllincoln]

Hi Sash,

How about creating a dummy extra field in your import table, allow it to be
null? Would that work?

Or, have you considered preparsing that data - to clip off the trailing
comma? You said the extra comma was on every row, you could read the file
in with a function that truncated that char off. Something like the below.
You could make this more elaborate, set it up to read all of the files
(probably using the Dir function) in a newdata folder, move the input files
to a completed folder as they are processed, and import each output file
after it's trailing comma has been truncated. Instead of using a Const for
the filename, you would use the Dir function to return the myInFile and
maybe prepend a prefix like myOutFile = "clean_" & myInFile or you could
import each myOutFile as it is created - then overwrite it with the next
file, import, etc.

Const myInFile as String = "C:\myfolder\myInFile.csv"
Const myOutFile as String = "C:\myfolder\myOutFile.csv"

dim fin as integer
dim fout as integer
dim s as string

fin = FreeFile()
Open myInFile for input as #fin

fout = FreeFile()
Open myOutFile four output as #fout

Do While Not EOF(fin)
Line Input #fin, s
Print #fout, Left(s, InstrRev(s, ",")-1)
Loop
Close #fin
Close #fout


Hope this helps,
Gordon