Importing Text file with Extra Delimiters - how to delete?

[lobo3101]

I'm importing text files that are comma-delimited. Unfortunately, the software that creates these files drops 2 extra commas in at the end of each line. This throws off my import and Access sees a "No Name" field there.

What's the best way to delete these commas? Is there an RTrim that'll read in the text file and delete the last 2 characters of each line?

I'm using "DoCmd.TransferText acImportDelim..." to load these texts into tables.

Here's an example of a line:
800.55,0,Alpha.450,1,Open,,

Those last 2 commas need to come out of every line in the text file.

 

[Remou]

Why not create an import specification with Skip selected for the last field?

 

[lobo3101]

Yanno, I'm not familiar with that specification. I tried to search google on it but couldn't come up with anything solid.

Could you provide anymore detail?

 

[Remou]

Import a text file manually and choose Advanced from the import window. It will allow you to edit and save a specification that can be used with TransferText.

 

[lobo3101]

I've checked those last 2 fields as "SKIP" in the Advanced portion of Import Table and saved it.

Still receiving this error though:
"Field NoName doesn't exist in table tblHoldings_Exception"

All fields are mapped and accounted for...but I think for some reason those 2 extra commas are still being counted in. Even though I've skipped them.

 

[Remou]

Please post your TransferText line.

 

[lobo3101]

DoCmd.TransferText acImportDelim, HoldingsException, "tblHoldings_Exception", _
"\\Folder\Reports\" & "Holdings Exception Report-" & today & ".txt", True

rstC.Open "select * from tblHoldings_Exception", conn, adOpenDynamic, adLockOptimistic
Do Until rstC.EOF
rstC.Update
rstC.MoveNext
DoEvents

------------------------------
HoldingsException is where I created the Import structure to Skip the last 2 fields.

 

[Remou]

So, is that:

Dim HoldingsException
HoldingsException="HoldingsException"

or

DoCmd.TransferText acImportDelim, "HoldingsException", "tblHoldings_Exception", _
        "\\Folder\Reports\" & "Holdings Exception Report-" & today & ".txt", True

In other words, the specification must be a string.

 

[lobo3101]

That was exactly it! The HoldingsException needed "" around it!!

Thankyou so much. None of the examples I looked at online had quotes there so I didn't think I'd need them. Very good!