importing CSV file - problem with data conversion

[Nevy]

Hi when I import the CSV file, everything is ok, except one column, which contains IP addresses.

So instead of having 10.195.7.252, I gets truncated to 10.195 or 10.1957

I'm assuming it as to do with the dot, and it's taking it as a decimal...

The frustrating part is that if it's done manually (File>import...) it works without a problem.

Any ideas?

This is the code I used:

DoCmd.TransferText acImportDelim, , "tempContent", strFilePath, True, ""

 

[cable]

use an import/export spec, do it manually but don't press finish at the end, press advanced...you can then save the spec and put the name of it as the 2nd option of the transfertext sub.

The other method is to write a manual text import routine, the one I use, uses the destination table as the 'import spec'

 

[Nevy]

Ah, that's what the second parameter is used for.

I've just tried that and it still gives me wrong IPs. I can't change the decimal delimiter. By default it's a dot, and I need it for other values.

And what is a "manual text import routine" ?

 

[mtcannady]

I am now having this same issue with IP addresses being 'compressed' when imported from a CSV file via acImportDelim. The target field is set to 'short text' with 255 chars. Have tried to set up manual import specs that will give me the correct IP addresses but then I lose the date/time values from the import file.


Seems very odd that I am having this same exact issue but there is nothing else to be found online.


Any suggestions??


Cheers.

 

[mtcannady]

Pat, thanks for the reply.

Unfortunately, my import files are all CSV format and not really affected by XL file type formats. When I manually create an import spec for 'short text' into my Access table, the IP address come through properly. It is only when I leave all the settings to default (w/out an import spec), do they get 'compressed'. I think Access is reading the field as a number type and rounding it to a single decimal point.

Ironically, also when setting a manual import spec with an appropriate date/time setup (mm/dd/yyyy hh:mm:ss), it will not import the date values and returns a 'data type conversion error'. Here is a sample of my date/time field: 1/1/2018 10:07:48 AM. This is consistent for all records that I am importing.

Ultimately the solution was to import the date/time field as short text into a 'temp' table and then reformat it with a query that also separates the date from the time values into my 'prod' table.

Check the spreadsheet. Excel may have the IP column defined as General rather than text. Access and Excel decide based on the first 20 or so rows what data type each column is. You need to force Excel to think this is text.

When you create an import spec, you can also set the date data type. Press the Advanced button to get to where you can set it. The problem is that ALL dates in the file have to be the same format so you can't mix yyyymmdd with mm/dd/yyyy. If you have a mixture, choose the most common type and import the other fields as text. Convert them in Access.

 

[isladogs]

Try forcing the datatype in another way.
CStr(IPAddress) and CDate(DateField)

I normally import to a temp or buffer table then convert as above when importing to the final table where there is any risk of possible error

 

[Tieval]

Ironically, also when setting a manual import spec with an appropriate date/time setup (mm/dd/yyyy hh:mm:ss), it will not import the date values and returns a 'data type conversion error'. Here is a sample of my date/time field: 1/1/2018 10:07:48 AM. This is consistent for all records that I am importing.

In my experience Excel can deal with this type of date but Access cannot, you need to change the 1/1/2018 to 01/01/2018, I usually fix this in a temp table.