PDA

View Full Version : convert cvs to xls - Semicolon delimited



frhling
12-15-2012, 02:41 PM
Everyday some csv files are generated under linux.
Now everyday the files should be converted to xls.

I have found a code:

Sub ConvertCSVtoXLS()
On Error GoTo ConvertCSVtoXLS_Err
Dim wb As Workbook
Dim nCount As Integer
Dim sSourceDir As String
Dim sTarget As String
Dim MyFile As String
Dim MyNewXLFile As String
'I assume all files are in the same path
sSourceDir = "c:\TestDir\CSVDir\"
If Right(sSourceDir, 1) <> "\" Then sSourceDir = sSourceDir & "\"
'where to put the renamed files
sTarget = sSourceDir
'Set some counters
nCount = 0
'Set environment
Application.ScreenUpdating = False
Application.DisplayStatusBar = True
Application.DisplayAlerts = False
' Windows platform search syntax.
MyFile = Dir(sSourceDir & "*.csv")
Do While MyFile <> ""
nCount = nCount + 1
Application.StatusBar = "Processing file " & nCount
Debug.Print sSourceDir & MyFile
'Open .csv file
Application.Workbooks.OpenText sSourceDir & MyFile, , , xlDelimited, , , , , True
'Convert column A text to columns
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False,
Semicolon:=True, Comma:=False, Space:=False, Other:=False
Range("A1").Select
'Save as excel
MyNewXLFile = Left(MyFile, Len(MyFile) - 4) & ".xls"
'NOTE: This will over-write if an XLS file already exists
ActiveWorkbook.SaveAs sTarget & MyNewXLFile, xlNormal ActiveWorkbook.Close False
DoEvents
'Optionally: Rename the .csv file so we don't convert it again
Name sSourceDir & MyFile As sSourceDir & MyFile & ".done"
'Get ready for the next file
MyFile = Dir()
Loop ConvertCSVtoXLS_Exit:
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.DisplayStatusBar = False
Exit Sub ConvertCSVtoXLS_Err:
MsgBox Err & " - " & Error
Resume ConvertCSVtoXLS_Exit
End Sub


seems it doesnt work by semicolon. as all goest into 1 cell.
I just chaned: Semicolon:=True and all other things False. I dont know what else should be changed. I am NULL in VBA Programming.

is there anyone who can help me?

patel
12-16-2012, 12:27 AM
Sub semicolon1()
Workbooks.OpenText Filename:= _
"C:\filename.csv", Origin:= _
xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:= _
False, Space:=False, Other:=True, OtherChar:=".", FieldInfo:=Array(1, 1) _
, TrailingMinusNumbers:=True
End Sub

snb
12-16-2012, 03:48 AM
the only thing you have to do:


Sub M_snb()
workbooks.open "G:\OF\Beispiel.csv"
End Sub

Aussiebear
12-16-2012, 02:57 PM
the only thing you have to do:


Sub M_snb()
workbooks.open "G:\OF\Beispiel.csv"
End Sub


What happens if some of the contained data has leading zeros? There is a high risk they may become truncated. Excel has some clear procedures to evercome this, which is probably why patel used the other method.