Pull .csV files into Database table Using VBA

[zach9208]

I have tried several different code changes to convert this macro code to pull in .csv files instead of .xls files. Can someone help me figure out how to make this work? All of the other forums that I have found do not provide a solution that works. Thanks in advance.

Public Sub PullData()

Dim strPathFile As String, strFile As String, strPath As String
 Dim blnHasFieldNames As Boolean
 Dim intWorksheets As Integer


' Replace 3 with the number of worksheets to be imported
 ' from each EXCEL file
 Dim strWorksheets(1 To 1) As String


' Replace 3 with the number of worksheets to be imported
 ' from each EXCEL file (this code assumes that each worksheet
 ' with the same name is being imported into a separate table
 ' for that specific worksheet name)
 Dim strTables(1 To 1) As String


' Replace generic worksheet names with the real worksheet names;
 ' add / delete code lines so that there is one code line for
 ' each worksheet that is to be imported from each workbook file
 strWorksheets(1) = "Test"




' Replace generic table names with the real table names;
 ' add / delete code lines so that there is one code line for
 ' each worksheet that is to be imported from each workbook file
 strTables(1) = "1- CMSN_STGNG"




' Change this next line to True if the first row in EXCEL worksheet
 ' has field names
 blnHasFieldNames = True


' Replace C:\Documents\ with the real path to the folder that
 ' contains the EXCEL files
 strPath = "M:\Commissions Database\Load Folder\"


' Replace 3 with the number of worksheets to be imported
 ' from each EXCEL file
 For intWorksheets = 1 To 1

     strFile = Dir(strPath & "*.xls")
      Do While Len(strFile) > 0
            strPathFile = strPath & strFile
            DoCmd.TransferSpreadsheet acImport, _
                  acSpreadsheetTypeExcel9, strTables(intWorksheets), _
                  strPathFile, blnHasFieldNames, _
                  strWorksheets(intWorksheets) & "$"
            strFile = Dir()
                
                   
              '[COLOR=#660066][FONT=inherit]DoCmd[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#660066][FONT=inherit]TransferText[/FONT][/COLOR][COLOR=#000000][FONT=inherit] acImportDelim[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#000000][FONT=inherit] strTable[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#000000][FONT=inherit] strPathFile[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#000000][FONT=inherit] blnHasFieldNames  - Tried using this line instead of the above but that did not work,[/FONT][/COLOR]
                
       Loop


 Next intWorksheets


End Sub

 

[xenou]

Hi,
You probably shouldn't be thinking in terms of "changing" this to work with csv files instead of Excel. They are so different that your entire code here needs to be mostly scrapped. So just start over, and basically write a new code to import a csv file.

 

[zach9208]

Well technically these .csv files are created by saving a regular .xlsx excel file and saving over to .csv. I am not creating these in another program such as notepad.

Hi,
You probably shouldn't be thinking in terms of "changing" this to work with csv files instead of Excel. They are so different that your entire code here needs to be mostly scrapped. So just start over, and basically write a new code to import a csv file.

 

[Micron]

Just a stab, but I figure why try to grab data from any .xls* file type if you're looking to grab from an Excel created .csv file? Maybe change
strFile = Dir(strPath & "*.xls") to strFile = Dir(strPath & "*.csv")?

 

[xenou]

Hi,
It really doesn't matter whether you created your csv files with NotePad or with Excel. They are still CSV files. Everything about worksheets and tables in your code doesn't apply (and you don't seem to be using it anyway). You probably just need to use the TransferText method, that's all. I guess if you want to you can leave all the Excel-related stuff in there, and just not use it - it won't hurt anything. On the other hand, it won't open any Excel files anymore either so I'm not seeing any reason to think of this as having anything to do with Excel.

 

[xenou]

Here is an example that works for me (csv file, importing from a file called zips.csv to a table called Zips, and the file has field names (column headers):

Sub foo()
    DoCmd.TransferText acImportDelim, , "Zips", "C:\myTemp\zips.csv", True
End Sub

 

[Micron]

Xenou - Based on post 3 and "pull in .csv files" I thought there might be a procedure underway, such as Automation. Not much info about what " pull in .csv files" means, but if you intend to use automation on the csv file using Excel objects, then a csv file opened in Excel looks the same as a spreadsheet, no? So I figured the column and row format would apply.
You are probably on the right track though.