problem opening German .csv file with columns seperated by ;

[jakeb3482]

Ok so I can open the file manually fine but when I try to run a script to open and convert the files I can not get the formatting right. The columns do not separate.

I tried this to no avail:

Filename = german_file
Workbooks.OpenText Filename:=german_file, DataType:=xlDelimited, Tab:=False, Semicolon:=True, Comma:=False, DecimalSeparator:=",", ThousandsSeparator:="."

The German numbering uses the period as the thousands separator and the comma as the decimal separator. When opening the file manually, I change these settings under the International Options tab and then I choose semicolon in the Text to Columns option.

any help would be greatly appreciated.

Thanks!
Jake

 

[Weaver]

Did you try using 'record macro' while you performed the action manually?

When I tried it (on 2002), I got:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 15/03/2011
'
'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Documents and Settings\xxx\Desktop\data.txt", Destination:= _
        Range("A1"))
        .Name = "dataset"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileDecimalSeparator = ","
        .TextFileThousandsSeparator = "."
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

HTH

 

[jakeb3482]

Thanks man. This seems to work although during the second loop of my script when i try to open the second file, it crashes and gives me an error. Also, if I try to rerun the script after it crashes, I get an error on the first file that previously opened fine.

The error I get stops here:
.Refresh BackgroundQuery:=True

Erorr:
Run Time Error 1004
Application-Defined or Object-Defined error

If I restart excel the first file will open again and then it crashes on the second file.

 

[Andrew Poulsom]

Are you sure that the second file actually exists?

 

[jakeb3482]

yes. I am using the dir() command to search for the .csv files in the directory.

I messed with a few settings in the With block you provided and I restarted excel and it worked.

Code:
Sub convert_mft_aku()
Application.DisplayAlerts = False

With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = "T:\Homestead_Test_Data\RTP-WLT\"
.Title = "Please Select Folder with Wafer Files"
.Show
test_dir = .SelectedItems(1) & "\"
End With

mft_wfr = Dir(test_dir & "*.csv", 7)

r = 1
Do While mft_wfr <> ""

'Application.DisplayAlerts = False

wfr_path = test_dir & mft_wfr
save_dir = test_dir & "converted\"
Workbooks.Add
new_book = ActiveWorkbook.Name

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & wfr_path, Destination:=Workbooks(new_book).Sheets("Sheet1").Range("A1"))
.Name = "dataset" & r
.FieldNames = True
.RowNumbers = True
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileDecimalSeparator = ","
.TextFileThousandsSeparator = "."
.TextFileTrailingMinusNumbers = True
'On Error Resume Next
.Refresh BackgroundQuery:=False
End With

last_col = find_in_row("", 1, 1) - 1

Columns(last_col).Select
Selection.Replace What:=",,*", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

With Application
.DecimalSeparator = "."
.ThousandsSeparator = ","
.UseSystemSeparators = True
End With


cur_file = ActiveWorkbook.Name

convert_dir = save_dir & mft_wfr

ActiveWorkbook.SaveAs Filename:=convert_dir, FileFormat:=xlCSV
ActiveWorkbook.Close (True)

'get next file
mft_wfr = Dir()
r = r + 1

Loop

End Sub


Function find_in_row(search_term, row, start) As Integer
counter = start - 1
Do
counter = counter + 1
Loop While Cells(row, counter) <> search_term
find_in_row = counter
End Function

 

[jakeb3482]

One question, how do you paste code properly into the message?

 

[Andrew Poulsom]

One question, how do you paste code properly into the message?

See:

http://www.mrexcel.com/forum/misc.php?do=bbcode#code

 

[jakeb3482]

Thank you. Also, here is the error I get when it does not open the file:

Run-time error '-2147417848 (80010108)
Automation error
The object invoked has disconnected from its clients.

If I restart excel, I do not get this error until I run the program and then stop it.

 

[Andrew Poulsom]

Can you post all your code please and tell us on which line you get that error?

 

[jakeb3482]

The code I pasted a few messages above is pretty much all of it except after the conversion I open a normal csv file that I put some summary data into. It seems opening that file is causing the above error to happen on the second file conversion of the loop. The error occurs on the .refresh background query line.