Import csv with US regional settings (1 Viewer)

jaryszek · Jan 16, 2019

Hi,

i am trying to import CSV with US regional settings and getting error like here:

https://support.microsoft.com/pl-pl/help/173828/access-step-by-step-page-11-cannot-import-01client-txt

the solution is not useful at all and demanding a lot of moves.

How can i automatically pull data using Access and different regional settings?
I am in Poland and i do not want to change my regional settings only to import one file to Access.
Do you have maybe workaround for this?

Best,
Jacek

isladogs · Jan 16, 2019

Import the data to a buffer or staging table as an intermediary step.
Process it to convert the data to your local setting.
Import the processed data to your final table
Empty the buffer table (or delete it if this is a one off operation)

jaryszek · Jan 16, 2019

Hi,

how to import data to buffer table if wizard for importing data has this problem?

Bet,
Jacek

isladogs · Jan 16, 2019

Don't use the import wizard and/or setup your own import specification.

If you are using CSV files, make sure the staging table fields are all text.
Then do any conversion from that to your final table using e.g. CDate, CLng, CDbl and formatting as necessary for dates or decimal points/commas etc

jaryszek · Jan 16, 2019

Hi,

i can not set up my own specification when importing data like here:

i can not even load external table from csv file...

Best,
Jacek

MajP · Jan 16, 2019

I think I would try to do a find and replace on the , and replace with another delimiter. Import as text as stated. Then import into the staging table. Then do clean up on those fields with the delimiter inside of them.

The other option may be to read your text file line by line. Most of the code you need is in the following thread, but the thread is more involved than what you would need since their input was not formatted as a CSV.

https://www.access-programmers.co.uk/forums/showthread.php?t=300423

However, with that said changing regional settings back and forth would be the simplest, IMO. Once you get the hang of it, it is not too hard.

isladogs · Jan 16, 2019

Have you tried importing the text file direct to the buffer table which you've created in advance where all fields are text fields?
If that fails, what does it object to?

jaryszek · Jan 16, 2019

Hi Guys,

Colin,
yes i tried. it is the same error.

It was really close with using docmd.textransfer but one column instead of being imported as text is imported as number and causes errors.

The error is not appearing when i am opening this csv file and saving it as excel.xlsx..
Maybe i have to open csv in Excel and save Excel file using vba?
This will avoid changing regional settings.

Best,
Jacek

isladogs · Jan 16, 2019

If you give it a chance, Access will 'intelligently' make judgements about the datatype. It does do by checking the first few records to see what those contain. Whilst it usually works well, it's a pain when it doesn't.

You could try one of the following
1. link the CSV file instead if importing it. Import the data to your prepared buffer table where all fie,ds are text...then process and transfer to your final table.
2. Open your CSV file in Excel. Convert number values to text by adding a leading single quote e.g. '3.5. Save. Import or link the CSV file

There is a free Excel add in called ASAP Utilities which will make that really easy. It has over 100 useful features
See https://www.asap-utilities.com/

jaryszek · Jan 18, 2019

hi Guys,

thank you!

2. I have a macro for that so this would be not a problem.
1. First seems also ok.

Can i change simple delimeted sign from "," to for example "?
Because Us guys can change csv format for me but i do not know what it should look like.

Best,
Jacek

jaryszek · Jan 18, 2019

I tried your idea with adding "'" before each of text string but this is not working:

https://www.access-programmers.co.uk/forums/attachment.php?attachmentid=73449&stc=1&d=1547800102

i can open csv in Excel, convert text to columns and add ' before each string but when i will save it as xlsx filr even without adding ' it will be working.

Best,
Jacek

isladogs · Jan 18, 2019

Can you upload the CSV file itself with no changes made.
Also what are your own regional settings?

jaryszek · Jan 18, 2019

my regional settings are polish, so i have comma instead of dot like in US settings.

I added csv file zipped.
Thank you Colin,

i am thinking to write code to change regional settings with VBA.
I hate when i have to do something manually...

best,
Jacek

isladogs · Jan 18, 2019

That only has 1 row. Can you upload something more realistic with multiple rows. At least 6 needed and preferably more to test import process

Also please confirm which field(s) are causing problems and what datatype these should be.

jaryszek · Jan 18, 2019

All data should be treated as text,
when it will work with this one row - it will work with hundreds because the problem is with fields OSVersion, it should be text and has 12.3 (i suppose?)

I have the same error here.

Thank you Colin,
Jacek

isladogs · Jan 18, 2019

See post 9 to understand why I'm asking for multiple rows.

jaryszek · Jan 18, 2019

Ok,

added few rows and changed OSversion to be different.

Best,
Jacek

isladogs · Jan 18, 2019

You seem to be wanting 2 contradictory things here
1. The decimal point to be changed to commas e.g. 12,3 as in Polish number settings
2. The OSVersion field to be text which will retain the decimal point

Attached is a zip file with example db & linked csv file.
To specify text for that column when relinked, do what the screenshot shows.

I have created a local table for importing to & 3 versions of an append query for you to try out.

jaryszek · Jan 18, 2019

wow thank you very much,
problem is that i can not relink the file so i started to create link once again using wizard.

And still have the same problem with linking tables :
"Text file specification field separator matches decimal separator or text delimiter"

Best,
Jacek

jaryszek · Jan 18, 2019

Colin,

i found out that when i will change decimal simbol in get external source wizard i have:

And this is working like a charm
Can i do something similar with VBA?

Best,
Jacek

Import csv with US regional settings (1 Viewer)

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Registered User.

Attachments

You've got your good things, and you've got mine.

MVP / VIP

Registered User.

MVP / VIP

Registered User.

Registered User.

Attachments

MVP / VIP

Registered User.

Attachments

MVP / VIP

Registered User.

MVP / VIP

Registered User.

Attachments

MVP / VIP

Attachments

Registered User.

Attachments

Registered User.

Attachments

Similar threads

Users who are viewing this thread