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]

@zmbd
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 "acSpreadsheetTypeExcel97" 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.TransferSpreadsheet 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.TransferText. 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","Header4","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.

[ADezii]

My concept is to perform ALL the work in Access, and then Export the Results to Excel. If this approach is acceptable, here is the 1st piece of the puzzle:

1. Open the Microsoft Office File Dialog Box filtered for *.csv Files only and the MultiSelect Property set to True.
2. The Default Folder to search in will be the Project Directory, namely CurrentProject.Path.
3. Select as many *.csv Files as you like, then click the Import Command Button.
4. All *.csv Files will now be Imported into your Access DB with Table Names reflective of the Base File Names only, as in:
   

   Inv 015 EDW FULL 20130716
   Inv 102 PAX FULL 20130716
    

5. At this point it should be a relatively simple matter to extract your Data, append it to a Results Table, then Export to Excel.
6. If you are interested in this approach let me know, and if not there are other alternatives which you can take.
7. I have posted the Code that will actually automate the process of Importing all the *.csv Files for your reference.
   

   'First, set a Reference to the Microsoft Office XX.X Object Library
   Dim varItem As Variant
   Dim strButtonCaption As String
   Dim strDialogTitle As String
   Dim strHyperlinkFile As String
    
   'Define your own Captions if necessary
   strButtonCaption = "Import"
   strDialogTitle = "Select CSV File(s) to Import"
    
   With Application.FileDialog(msoFileDialogFilePicker)
     With .Filters
       .Clear
         .Add "CSV Files", "*.csv"     'Allow only *.csv File types
     End With
     'The Show Method returns True if 1 or more files are selected
       .AllowMultiSelect = True
       .ButtonName = strButtonCaption
       .InitialFileName = CurrentProject.Path
       .InitialView = msoFileDialogViewDetails     'Detailed View
       .Title = strDialogTitle
     If .Show Then
       For Each varItem In .SelectedItems
         DoCmd.TransferText acImportDelim, , Replace(Mid$(varItem, InStrRev(varItem, "\") + 1), ".csv", ""), _
                            varItem, True
       Next varItem
     End If
   End With

[mugwy2264]

Hope this helps, thanks for assistance so far:
File names I refer to as 102 are like this : Inv 102 EDW FULL 20130715.csv
File names I refer to as 015 are like this: Inv 015 EDW FULL 20130715.csv
And are stored, once extracted in a file location ending in ….\Daily csv reports. The 20130715 suffix changes daily such that today’s files would end 20130717.

From those prefixed “102”
column A,(title is site)B,(title is Part No),C,(title is Part Description),D,(title is Serial No),E,(title is Lot/Batch No),G,(title is Condition Code Description), & J,(title is Qty Onhand)

from “015” column A,(title is Site),B,(title is Part No),C,(title is Part Description),F,(title is Lot/Batch No),G,(title is Serial No),I,(title is Condition Code Description), & R,(title is Qty Onhand
This is the full list from 015:
Site Part No Part Description Lot/Batch No Serial No Condition Code Condition Code Description EC W/D/R Warehouse Bay Row Tier Bin Rotable Part Pool Id Qty Onhand Qty Reserved Qty In Transit U/M Freeze Flag Last Activity Date Last Count Date Location Type Receipt Date Availability Control Id Supplier No Avg Unit Transit Cost Count Variance Expiration Date Ownership Owner Owner Name Part Acquisition Value Total Acquisition Value Acquisition Currency

[mugwy2264]

ADezii
The ACCESS route would be fine, thanks for help so far

[ADezii]

No problem, I will not write all the Code for you since this is not a code writing service. If you execute the beginning Code that I have provided, you will have all your *.csv Files Imported into your DB as Tables named after the Base Names. Do you know how to proceed after this?

[zmbd]

Take what ADezii has for you and then follow thru on the concept in Post#13.
The export back to excel can be done via the trasnsferspreadsheet method as mentioned in post#7.
Be aware though that the trasnsferspreadsheet uses a "stored" query (one that you can see in the Access Object Navigation Pane) and will not take a SQL string as one would expect in VBA. There are several threads about this here on Bytes.

[zmbd]

Hey,
My mind said, why involve Access... I like Access, I think that OP would be well advised to move eveything to Access; however, back to that little voice in my head, what about treating the CSV as a database via the ADODB connection.

AH-HA-I-Said...
And I have a nice little late bind method that pulls the data in and, for now, prints the values to the debug screen.
I just need to tweek the monster a bit and records will go to the worksheet.

Here's just enough to wet the gums and teeth:

Sub OpenTheDataFileForReading(ByVal zstrinfolder As String, ByVal zstrinfile As String)
Dim zobjcn As Object
Dim zrs As Object
Dim zstrcn As String
Dim zstrdatadirectory As String
Dim zstrdatasource As String
Dim zstrSQL As String
'
'get the file
zstrdatadirectory = zstrinfolder
'
zstrdatasource = "[" & zstrinfile & "]"
'
'build the connection string
zstrcn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & zstrdatadirectory & _
    "; Extended Properties=""text;HDR=Yes;FMT=Delimited(,)"";"
'
'open the connection
Set zobjcn = CreateObject("ADODB.Connection")
If zobjcn Is Nothing Then
    MsgBox "Failed to create the object, sorry"
    Exit Sub
Else
(...)The rest omitted for now
While I tweek the code(...)

[zmbd]

Done - ofcourse, this is in v2010 as a macro enabled workbook (*.xlsm); however, I am fairly certain that it will work in v2007.

It can have more bells and whisles added to it; however works quite nicely.

To keep some things simple, I've used the FileDialogObject with the multiple select set to true. The only issue I've had with that is that no matter how one selects the files, they are pulled from the selecteditems collection in numericalpha order (so all of the 015 prefix would import before all of the 102 etc). Can either tag these in an adjacent column, within the rows, or finally create a new worksheet for each imported file.

:)

[ADezii]

@zmbd:
Another approach would be to grab each Line of the Files using Low Level I/O and immediately Append the Field's Data to a pre-existing Results Table. The Fields to extract would vary from File to File, but in the case of Inv 102*.csv:

Dim strPathToFile As String
Dim strLine As String
Dim varLine As Variant
Dim MyDB As DAO.Database
Dim rstResults As DAO.Recordset
Dim intLineNum As Integer
 
Set MyDB = CurrentDb
Set rstResults = MyDB.OpenRecordset("tblResults", dbOpenDynaset)
 
strPathToFile = "C:\_Transfer SCV Files\Inv 102 PAX FULL 20130716.csv"
 
Open strPathToFile For Input As #1
 
'For File Inv 102, extract Columns A,B,C,D,E,G, and J
Do While Not EOF(1)                     'Loop until end of file.
  Line Input #1, strLine                'Read line into variable.
    intLineNum = intLineNum + 1
      varLine = Split(strLine, ",")     'Parse the Line using ','
        With rstResults                 'Results Table
          If intLineNum <> 1 Then       '1st Line is Field Names, ignore
            .AddNew                     'Fields to extract for Inv 102*.csv
              ![Site] = varLine(0)
              ![Part No] = varLine(1)
              ![Part Description] = varLine(2)
              ![Serial No] = varLine(3)
              ![Lot/Batch No] = varLine(4)
              ![Condition Code Description] = varLine(6)
              ![Qty Onhand] = varLine(9)
          .Update
        End If
      End With
Loop
 
Close #1                                'Close file.
rstResults.Close                        'Cleanup
Set rstResults = Nothing

[zmbd]

ADezii,
You never fail to impress!! I'll be adding that code to the tool box for future reference.

The reason I went with the ADODB is that the field names are the same between both 102 and 015 prefix; thus, the select query only needs to be written in one format to import from both prefixed files.

[ADezii]

The reason I went with the ADODB is that the field names are the same between both 102 and 015 prefix; thus, the select query only needs to be written in one format to import from both prefixed files.

Makes perfect sense to me, and a great approach! Either way, the OP had several options to take, but he/she still has a job ahead! (LOL).

[zmbd]

that's an understatement.