• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple CSV to Access

C

coolkiran

Member
Hello to everyone,

I have several csv, around 100, All csv in same format, like Date, time, open, high, low, close, volume,

I need these csv to merge in Access table, with that i need one more column as ticker in each row of access table, ticker means part of csv file name for example : CVX_60 is the filename, i want CVX as value for all rows for that csv similarly for all files, Here filename is also standard, it will be like tickername_somenumber

Please assist
 
Hi, coolkiran!
I don't understand. You said you have 100 .csv files with the same format, and that you want to merge them into an Access table. Where's Excel involved? Code shouldn't be in Excel macro but in Access procedure.
Regards!
 
Ok, i don't know, whether its possible, In excel sheet, i will add some button, once user click that button, it should browse to folder where my csv files located, and my access file will be in predefined path, so i know the file name, path and table name, export all csv file to that access table.

That was my one of the function in User form, i have created some activities in my excel file, like merging several excel to one excel (For that i have button), ... etc. If i have completed this part also it looks good.
 
Hi, coolkiran!

Yes, it can be done from Excel, and from Word too, but it'd be a little cumbersome as like scratching your left ear with your right hand going under your left arm. So why don't doing it in the normal way? Left ear, left hand. Into Access? From Access!

And I apologize if being impaired, it's just a metaphor.

Regards!
 
Last edited:
SirJB7 said:
Hi, coolkiran!

Yes, it can be done from Excel, and from Word too, but it'd be a little cumbersome as like scratching your left ear with your right hand going under your left arm. So why don't doing it in the normal way? Left year, left hand. Into Access? From Access!

And I apologize if being impaired, it's just a metaphor.

Regards!
Click to expand...

Thanks :)

I have rarely worked in Access, However, i will check.

Thanks alot
 
Hi, coolkiran!

Now that we've settled for the better method, a few questions:
a) Could you upload a sample Access .accdb database file with that table definition at least? So as to check structure, fields data type, primary key, indexes, etc. And a couple of sample .csv files too.
b) Among the 100 .csv text files are there any duplicate records, and if so, which fields match with the PK and other unique indexes from a)?
c) Which is the average no. of records for each .csv file?
d) What should be done if an existing record exists in the table?
e) What should be done if there're records in the table, prior to importing process?

Regards!

EDITED
 
Last edited:
SirJB7 said:
Hi, coolkiran!

Now that we've settled for the better method, a few questions:
a) Could you upload a sample Access .accdb database file with that table definition at least? So as to check structure, fields data type, primary key, indexes, etc. And a couple of sample .csv files too.
b) Among the 100 .csv text files are there any duplicate records, and if so, which fields match with the PK and other unique indexes from a)?
c) Which is the average no. of records for each .csv file?
d) What should be done if an existing record exists in the table?
e) What should be done if there're records in the table, prior to importing process?

Regards!

EDITED
Click to expand...

Great,

I have uploaded two files, one is Access file and second one is my csv file.

http://www.mediafire.com/download/x4be8ued2so6ta6/MyAccessDatabase.mdb

http://www.mediafire.com/view/0e7q6a7b21a6e0e/ALDW_60.csv

Regarding question: We will not get any duplicates, for every file we need to add part of file name at the end of each row in access (ticker) (i have entered one sample data in Access), So in single csv we will not get any duplicates, overall its not possible because last column 'Ticker' will change file by file.

Average row will come to around 20,000

If there is any record in access, after the last row we need to merge

Regards
 
Hi, coolkiran!
Where are the .csv text files located? In a dedicated folder or sharing one with other files? If sharing one, are there any other .csv files? If so, how to identify which ones should be imported?
Regards!
 
Hi, coolkiran!
I supposed so, otherwise they'll be... either networked or clouded, if not pendriven... ;)
I meant if they'll be in a dedicated folder where no other .csv file exists so as to process all .csv found files or if they should be filtered and selected.
Regards!
 
gosh this sounds tricky: if one of those csv files is a little off it will jack up everything. Power query supports some nifty administrative joins. You'd still have to do each one manually, working with 100 files, something is bound to go wrong.
 
Hi SirJB7

Correct me if understood correctly,

Here all my csv file we are storing in local drive, we will create one folder, in that folder which all csv is there we want to merge the data to access table, currently we have around 100 CSV, once we merged all data, we will move all files inside folder to another location, and new csv we will add to same folder later, again merging files inside the folder.
 
Hi, coolkiran!
Yes, I agree. Let us say the .csv are in folder C:\...\here, and after importing them into the Access DB they are moved to folder C:\...\here\done. Is that what you want?
Regards!
 
Correct,

Current problem is only merging all .csv data inside the folder to access table.

Once this done, we will move all files to some other location.
 
Hi, coolkiran!
Ok, let me try to find a bit of inspiration in my Carslberg muse... You're free to help me with a couple of six-packs! ;)
Regards!
 
You must log in or register to reply here.
Back
Top