batch opening CSV and saving CSV

**ssbbg** · 03-25-2013, 10:23 AM

Hello,
I am a complete novice to excel macros, and I would appreciate some help with a simple problem.

An instrument in our lab saves its data as CSV files. These are openable in MS Excel, but not other programs we use for data analysis (such as Matlab or Origin). If I open the file in excel, then save it (still as a csv) (though excel seems to default to unicode text when you go to resave it) it magically becomes readable by our other programs. I would like write a macro to open all files and resave them as CSV. All my data files have the format 1letters#.csv. So I can tell if the files have been resaved, I would like to save the new files as "A*.csv". So the original file might be 1abcA1.csv. After it has been resaved, it should be A1abcA1.csv.

I found a thread here: http://www.excelforum.com/excel-gene...rnal-data.html

which looks like it should be relevant. But, I think the loop is using the extension as the condition- since I am saving the file under the same extension, I think maybe it will cause an endless loop. However, I don't get that far. The modified code won't even compile.

Sub Macro1()
'
' Macro1 Macro
'

'
    Dim sFile As String
    Dim spath As String
    Dim wB As Workbook
    Dim wS As Worksheet
    
    spath$ = "C:\Users\User\Desktop\1LES95\"
    Set wB = Workbook.Add
    Set wS = wB.Sheets(1)
    sFile$ = Dir(spath & "1*.csv")
    
    Do Until sFile = ""
           Do Until sFile = ""
      If Right(sFile, 4) <> ".csv" Then
         With wB.Sheets(1).QueryTables.Add(Connection:= _
            "TEXT;" & spath & sFile _
            , Destination:=Range("A1"))
            .Name = sFile
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlFixedWidth
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1)
            .TextFileFixedColumnWidths = Array(16, 16)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
         End With
      
         wB.SaveAs Filename:=spath & & 'A' & sFile & ".csv" _
            , FileFormat:=xlCSV, CreateBackup:=False
         
         Application.DisplayAlerts = False
         wS.Cells.ClearContents
         Application.DisplayAlerts = True
      End If
      sFile$ = Dir
      Loop
   End Sub

When I record a macro to open and resave a single file, it looks very different from the sample code given in the previous thread. This is what I get:

Sub Macro3()
'
' Macro3 Macro
'

'
    Workbooks.OpenText Filename:="C:\Users\User\Desktop\1LES95\1LES95B1.CSV", _
        Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
    ActiveWorkbook.SaveAs Filename:="C:\Users\User\Desktop\1LES95\A1LES95B1.csv" _
        , FileFormat:=xlCSV, CreateBackup:=False
End Sub

I'm not sure what the difference is between OpenText and QueryTables.Add. I'm using excel 2007. The code in the thread above was for 2003. Is that the difference?

Thanks for your help.
ssbbg

**patel45** · 03-25-2013, 12:54 PM

there are many ways to get the same result, does the first code work ? you can use it

**HaHoBe** · 03-25-2013, 12:58 PM

Hi, ssbbg,

AFAIK the macro code should read

         wB.SaveAs Filename:=spath & "A" & sFile & ".csv" _
            , FileFormat:=xlCSV, CreateBackup:=False

instead of

         wB.SaveAs Filename:=spath & & 'A' & sFile & ".csv" _
            , FileFormat:=xlCSV, CreateBackup:=False

Ciao,
Holger

**ssbbg** · 03-25-2013, 01:25 PM

Originally Posted by patel45

there are many ways to get the same result, does the first code work ? you can use it

No, it doesn't work. I'm not sure why. Thanks.

**HaHoBe** · 03-25-2013, 01:38 PM

Hi, ssbbg,

maybe

'...
            .TextFileTabDelimiter = False  ' was: True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = True  ' was: False

Maybe you attach a file to check the macro both as an original and as a wanted outcome.

Ciao,
Holger

**ssbbg** · 03-25-2013, 01:46 PM

Holger,
Thank you! I don't know how I missed that. Also, the property needs to be ".TextFileParseType = xlDelimited" from fixed width.

Now to try looping, which may be extra fun.

Originally Posted by HaHoBe

Hi, ssbbg,

maybe

'...
            .TextFileTabDelimiter = False  ' was: True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = True  ' was: False

Maybe you attach a file to check the macro both as an original and as a wanted outcome.

Ciao,
Holger

**ssbbg** · 03-25-2013, 01:52 PM

Holger,
The loop worked perfectly. Thank you for all your help.

For completeness, here is my final code that works.

Sub Macro1()
'
' Macro1 Macro
'

'
    Dim sFile As String
    Dim spath As String
    Dim wB As Workbook
    Dim wS As Worksheet
    
    spath$ = "C:\Users\User\Desktop\1LES95\small loop test\"
    Set wB = Workbooks.Add
    Set wS = wB.Sheets(1)
    sFile$ = Dir(spath & "1*.csv")
    'sFile = "1les95A1.csv"
    
    Do Until sFile = ""
        
     If Right(sFile, 4) <> ".csv" Then
         With wB.Sheets(1).QueryTables.Add(Connection:= _
            "TEXT;" & spath & sFile _
            , Destination:=Range("A1"))
            .Name = sFile
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1)
            .TextFileFixedColumnWidths = Array(16, 16)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
         End With
      
          wB.SaveAs Filename:=spath & "A" & sFile _
            , FileFormat:=xlCSV, CreateBackup:=False
         
         Application.DisplayAlerts = False
         wS.Cells.ClearContents
         Application.DisplayAlerts = True
     End If
     sFile$ = Dir
      Loop
   End Sub

**ssbbg** · 03-25-2013, 01:55 PM

Holger,
The loop worked perfectly. Thank you for all your help.

For completeness, here is my final code that works.

Sub Macro1()
'
' Macro1 Macro
'

'
    Dim sFile As String
    Dim spath As String
    Dim wB As Workbook
    Dim wS As Worksheet
    
    spath$ = "C:\Users\User\Desktop\1LES95\small loop test\"
    Set wB = Workbooks.Add
    Set wS = wB.Sheets(1)
    sFile$ = Dir(spath & "1*.csv")
    'sFile = "1les95A1.csv"
    
    Do Until sFile = ""
        
     If Right(sFile, 4) <> ".csv" Then
         With wB.Sheets(1).QueryTables.Add(Connection:= _
            "TEXT;" & spath & sFile _
            , Destination:=Range("A1"))
            .Name = sFile
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1)
            .TextFileFixedColumnWidths = Array(16, 16)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
         End With
      
          wB.SaveAs Filename:=spath & "A" & sFile _
            , FileFormat:=xlCSV, CreateBackup:=False
         
         Application.DisplayAlerts = False
         wS.Cells.ClearContents
         Application.DisplayAlerts = True
     End If
     sFile$ = Dir
      Loop
   End Sub

**ssbbg** · 03-25-2013, 01:24 PM

Thanks! Ha! Ha! Funny mistake- That was Matlab slipping through- strings are denoted with 'string' instead of "string".

I did catch that when the forum was down, but the CSV file is not opening correctly. There was also an extra loop, and workbook.add instead of workbooks.add

I have commented out the loop part, and am just trying to get the open and close part to work (from the original thread)

I now have this:

Sub Macro1()
'
' Macro1 Macro
'

'
    Dim sFile As String
    Dim spath As String
    Dim wB As Workbook
    Dim wS As Worksheet
    
    spath$ = "C:\Users\User\Desktop\1LES95\small loop test\"
    Set wB = Workbooks.Add
    Set wS = wB.Sheets(1)
   ' sFile$ = Dir(spath & "1*.csv")
    sFile = "1les95A1.csv"
    'Do Until sFile = ""
         '  Do Until sFile = ""
     ' If Right(sFile, 4) <> ".csv" Then
         With wB.Sheets(1).QueryTables.Add(Connection:= _
            "TEXT;" & spath & sFile _
            , Destination:=Range("A1"))
            .Name = sFile
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlFixedWidth
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1)
            .TextFileFixedColumnWidths = Array(16, 16)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
         End With
      
          wB.SaveAs Filename:=spath & "A" & sFile & ".csv" _
            , FileFormat:=xlCSV, CreateBackup:=False
         
       '  Application.DisplayAlerts = False
       '  wS.Cells.ClearContents
       '  Application.DisplayAlerts = True
    ' End If
    '  sFile$ = Dir
    '  Loop
   End Sub

So, it does open and save a file, but it completely mangles it. I cannot find out how to tell it to understand that the file is comma delimited. I looked up "QueryTables.Add" and "connection" in the help, and it is pretty opaque. Nothing seems related to the setting a delimiter. All the subsequent .text stuff below the width also does not seem related.

Thanks again.
ssbbg

**patel45** · 03-25-2013, 01:51 PM

Sub OpenfilecsvSave()
Dim strFile As String
mFolder = "C:\Users\User\Desktop\1LES95\small loop test\"
sFile = Dir(mFolder & "*.csv")
Do While sFile <> ""
    Workbooks.OpenText fileName:=mFolder & sFile, _
        Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
    ActiveWorkbook.SaveAs fileName:=mFolder & "A" & sFile _
            , FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close True
    sFile = Dir
Loop
End Sub

**ssbbg** · 03-25-2013, 01:57 PM

Thank you Patel 45. I will study this carefully, as this looks more similar to what I got when I recorded the macro for opening a single file. What is the advantage of OpenText vs. QueryTables.Add?

Originally Posted by patel45

Sub OpenfilecsvSave()
Dim strFile As String
mFolder = "C:\Users\User\Desktop\1LES95\small loop test\"
sFile = Dir(mFolder & "*.csv")
Do While sFile <> ""
    Workbooks.OpenText fileName:=mFolder & sFile, _
        Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
    ActiveWorkbook.SaveAs fileName:=mFolder & "A" & sFile _
            , FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close True
    sFile = Dir
Loop
End Sub

**patel45** · 03-25-2013, 02:08 PM

this looks more similar to what you got when you recorded the macro for opening a single file because I copied it

batch opening CSV and saving CSV

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

batch opening CSV and saving CSV

Re: batch opening CSV and saving CSV

Re: batch opening CSV and saving CSV

Re: batch opening CSV and saving CSV

Re: batch opening CSV and saving CSV

Re: batch opening CSV and saving CSV

Re: batch opening CSV and saving CSV

Re: batch opening CSV and saving CSV

Re: batch opening CSV and saving CSV

Re: batch opening CSV and saving CSV

Re: batch opening CSV and saving CSV

Re: batch opening CSV and saving CSV

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions