Import CSV file - Field with numbers containing thousand space seperator

[pacoako]

Hi guys,

I was wondering if there was anyway to import a file containing numbers with a "thousand space separator"

example :

1 000


2 000
3 000
4 000 000
5 000 000 000

Only workaround I found so far is to import de numbers into a text field in a temp table, do an update query to remove the spaces and then transfert the data into the real table that has a numeric field

Thanks in advance for your help,

[ItsMe]

Often times I will import CSV files to a temp table where each column is of data type Text. Then I use VBA for data validation as I move the records over to their permanent home.

[pacoako]

Often times I will import CSV files to a temp table where each column is of data type Text. Then I use VBA for data validation as I move the records over to their permanent home.

Thanks for your answer ItsMe.

It's actualy what I do for now... I was just wondering is there was an easier way of doing this. Like an importation parameter I didn't think about...

[ItsMe]

If the entire portion of data to be imported is guaranteed to be structured and formatted correctly, you can avoid the data verification/validation and import directly into a table with your proffered data types. Otherwise, the only other way I am aware of is to parse the file and do data validation in your parse procedure (open file via automation and read every line).

[pacoako]

Thats very interesting! How do u turn it off?

[ItsMe]

... How do u turn it off?

Turn what off?

[pacoako]

Data vérification

[June7]

The data verification referenced by ItsMe is VBA code. I don't think you will be able to avoid VBA with that CSV structure.

[pacoako]

HI June. Thanks for ur reply.
Perhaps it would be interesting to investigate that path as well... Maybe i could. Automate something in vba... Do u have any piece of code i could start with?
Thanks 4 ur help

[June7]

VBA must open the CSV file as an object, read each line of the file, parse the string into an array, loop the array and do something with each element. In your case, will have to concatenate the number parts.

Bind: Access VBA line input CSV

http://programmersheaven.com/discuss...te-to-csv-file

[pacoako]

I will look foward to that tomorow. Do u know if this will be faster than importing in a temp table, update and transfer in final table?

[June7]

Probably not and the code is certainly more complicated.

And I revise my earlier comment. With the temp table process you currently use, can avoid VBA.

[pacoako]

Probably not and the code is certainly more complicated.

And I revise my earlier comment. With the temp table process you currently use, can avoid VBA.

Alright June. I think i'll try to automate the process throught the temp table instead

Thanks a lot for your time!

See ya

[ssanfu]

Try this on a COPY of the CSV file

You might also try opening the CSV file using notepad.
EDIT/REPLACE
In Find what, press the space key
In Replace with, press the comma

Click on the Replace All button.
Save the CSV file.

[pacoako]

Try this on a COPY of the CSV file

You might also try opening the CSV file using notepad.
EDIT/REPLACE
In Find what, press the space key
In Replace with, press the comma

Click on the Replace All button.
Save the CSV file.

Hi Ssanfu,

thanks for your post

The thing is that the files are pretty huge... approx 1million records. Doing search and replace via notepad is very slow unfortunatly