Importing via SQL: How can I make data come in as text?

  • Thread starter Thread starter Max Moor
  • Start date Start date
M

Max Moor

Hi all,

I'm using an SQL statement like below to import data from a comma
delimited text file. "tblImport" has all fields set as a text data type.
I've found that if my ZipCode data looks like "98765," things are
fine. If they look like "98765-4321," I get a numerical overflow error. I
assume that is because the first record has one of the short version,
without a dash, and the import code decides its numeric data.

Does this seem reasonable? I tried adding a cast in the select,
like:

CAST(F5 AS text) As ZipCode

but I get an error saying there is a missing operator. I guess I
don't even know if CAST is supported, as I can't find any documentation on
it for Access.

Is there a way I can, within the SQL statement, force the data to be
treated like text, regardless of its appearance?

- Max





strSQL = "Insert INTO tblImport (Name, Address) " & _
"SELECT F1 As Name, F2 As Address " & _
"FROM [Text;HDR=No;Database=" & strPath & ";]." & strFilename & "#" &
strExtension
 
Hi,


CString( field ), with JET, is to be used rather than CAST( field AS TEXT)
, with MS SQL Server.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top