can multiple csv files be merged into xls file dependant on column selection?

[mugwy2264]

I receive a number of different csv files daily. The data i need is contained in different columns in each (and the format cannot be changed before i receive the files). The file names are different and change on a daily basis with the suffix being yyyy/mm/dd. Can i automate the process of selecting the respective columns from each file, copying the records and then merging the records?

[zmbd]

Version of Office/Excel?
Do the CSV files have a "header" row?
Please tell me that they are not using the "/" in the file names...

More than likely, you'll have to import the entire document into a temporary worksheet and then parse out the information you need.


This is the basics for handling them manually (and I do mean basic :) ): Import or export text (.txt or .csv) files

[ADezii]

1. As asked by zmbd, is there a Header Row in the CSV Files?
2. Header Row for some or all of the Files?
3. How many CSV Files are there?
4. Are all CSV Files located in the same Folder?
5. Are the Base Names of the CSF Files consistent?
6. Does each CSV File represent the same number of Records in it as the others?
7. What are the Column Specifications, namely extracting which Columns from what CSV Files?
8. Provide some concrete Data, upload a *.csv File if possible.
9. etc...

[zmbd]

Actually, you need not upload the CSV file, if you will click on the [CODE/] format button and paste the header row and the first few rows of the data between the code tags. This way we can all review the data without having to download and open a file.

[ADezii]

Actually, you need not upload the CSV file, if you will click on the [CODE/] format button and paste the header row and the first few rows of the data between the code tags. This way we can all review the data without having to download and open a file.

The reason that I ask for an actual File Upload is that there may be Control Codes embedded in the File depending on the Originating Source. This could possibly affect the importing/extracting of such data. There is nothing like having the Original Data on hand to test as opposed to a graphical representation of it. Perhaps I am just getting a little too old! (LOL).

[zmbd]

Hmm, hadn't considered that there might be embedded control codes.
I blame that on a lack of diversity given that all of the CSV files I've worked with are pretty plan old boring alphanumeric with occasional literal strings in quotes kind of stuff. With that in mind, what had envisioned for OP was a direct copy and paste from the file itself so that anything that is "printable" would be available in a subset of data instead of potentially a huge number of records.

[jimatqsi]

You'll want to use Dir() to scan the folder containing the Excel files. As you find them, link to them with

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel97, "TableNameToUseForLink", SpreadSheetFileName, True, , True

to link to each spreadsheet. The "acSpreadsheetT ypeExcel97" parameter will change depending on the version of Excel involved.

Once you have a link to the spreadsheet you can open it on a recordset. Then you can loop through the rows and columns to figure out what you've got - but this requires that the spreadsheets follow some kind of regular pattern. The spreadsheets don't have to all have the same columns, but you have to be able to recognize the columns for what they are.

I hope that helps.

Jim

[zmbd]

jimatqsi:
- OP is talking about CSV files which I am aware that newer versions of Excel can open directly; however, to do requires human interaction to correlate the incoming data as these are still not native Excel files.
- Also, OP is wanting to import the files into Excel, not Access; therefore, the DoCmd.TransferS preadsheet command is not available. In fact, none of the DoCmd.(*) commands are available for use in Excel.


mugwy2264:
Yet, jimatqsi does have a one partial solution with the DIR() in that as ADezii points out if there is a common suffix/root to the files we can scan for these and build the import function. From there it will depend upon the method as to how we can extract the columns you require and discard the remaining chaff.

[ADezii]

My initial idea is to:

1. Open the Office File Dialog, with the Filter set to *.csv and MultiSelect = True.
2. Select all the necessary *.csv Files and Import them into Access Tables.
3. Incorporate some form of Validation making sure you have the required number of Files.
4. Extract the required Data from these Tables based on Name and Column, then Append the results to a Results Table.
5. Export the Results Table to Excel if so desired.

[mugwy2264]

1.As asked by zmbd, is there a Header Row in the CSV Files? Yes, see attached eg
2.Header Row for some or all of the Files? All.
I’ve attached 102 and 015
3.How many CSV Files are there? 6, differently named
4.Are all CSV Files located in the same Folder?
They will be
5. Are the Base Names of the CSF (csv?)Files consistent? Other than date suffix, yes
6.Does each CSV File represent the same number of Records in it as the others?
No varies between <8 and >20000 and there are more columns in some than others
7.What are the Column Specifications, namely extracting which Columns from what CSV Files? From those prefixed “102” columns A,B,C,D,E,G, & J from “015” columns A,B,C,F,G,I, & R. The header titles remain constant in name and position.

[jimatqsi]

Maybe the best way to handle it is with DoCmd.TransferT ext. Transfer each text file into a new table (because column layout is unpredictable) then examine the columns to figure out how to process it before deleting the new table. Here's a real good discussion on stackoverflow ... http://stackoverflow.com/questions/3...sv-file-in-vba

[zmbd]

mugwy2264
Are you taking this into Access or Excel?
For some reason I thought you started out with taking CSV texts into Excel workbook in your original post.

[zmbd]

Ops, should have added this to that last:

7.What are the Column Specifications, namely extracting which Columns from what CSV Files? From those prefixed “102” columns A,B,C,D,E,G, & J from “015” columns A,B,C,F,G,I, & R. The header titles remain constant in name and position

I can't open your files here at work, one reason I asked for a cut and paste of the the inforamtion, the attachment is OK as it was requested by ADEzii.

If the header 102.A == 015.A (header1), etc...
then let's change mind sets a tad...
If you are in Access, when you import the CSV, the header names will become field names in the table. Therefor, instead of refering to them as column A,B,C... lets start thinking in terms of what you need, the information under the headers named: "Header1", "Header2", "Header3","Head er4","Header5", "Header6", and "Header7".
(now I'm also hoping that the titles for 102.G == 015.I and that 102.J == 015.R)

So in Access, the thought is to import the CSV into a "New" table for each file. Your header names become field names. Run an update query against this new table on the field names ("header*") that you are after to merge this data into your working table. Finally delete your import table.

[mugwy2264]

I'm trying to bring the .csv files in to excel.

[zmbd]

That is what I thought.
Then NONE of the "docmd" VBA commands are available to you nor is the approach in Post#13.