text file import truncates data at null character 1

[jimlee]

Hi,

I have a text file which contains a null character which when trying to import into the database causes all the data after this character to be truncated.

I am trying to automate a process which will import this every day and the null value will always be there.

Please could someone suggest a way of either accepting the null values into the database without truncating them or getting rid of the null values from vba code before the import.

I have tried allsorts (opentextstream etc) but everything I can think of involves opening the file in some way first and then it's too late!

Any help much appreciated as always.
jimlad



jimlad

"There's this thing called being so open-minded your brains drop out." -

 

[Remou]

This may help:

http://groups.google.ie/group/micro...naldata/browse_thread/thread/139fd5d3d365e7e2

It seems to work.

 

[CautionMP]

jimlee,
For grins & giggles, if you query the file directly (without linking or importing) does the same thing happen?

Open a new query, close the Show Table dialog without choosing a table, switch to SQL view then use this type of pattern as your SQL string. Be sure to update the file name an path portion (in italics below) of the statement.

[tt]SELECT * FROM [FileName.txt] IN 'C:\SomePath' 'TEXT;HDR=NO;';[/tt]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[jimlee]

followed Remou's link, worked a treat thanks.

Thanks for both replies.

jimlad

"There's this thing called being so open-minded your brains drop out." -