Convert CSV(semicolon separated file) in to XLS

[Leosy]

Hello.

Is the any chance to convert csv file

DATE;DEPART;CLA;SERV;ERRE;MESE;SCHEE;TYP;DUREE;TAI;FICH;RE;Date o;Date f;Com
2012-05-07;21:02:04; da_nbondv02_data;nbondv02;0;OK;lmejvd-diff-2sem;da;00:02:20;41272;32;2 Sem
2012-05-07;21:00:00; da_nbondv02_sys;nbondv02;0;OK;lmmjvd-diff-2sem;da;00:04:04;356070;2109;2 Sem
2012-05-07;21:01:39; da_nbondv03_data;nbondv03;0;OK;lmejvd-diff-2sem;da;00:07:55;224387;3335;2 Sem

in to XLS ?

I need to open this CSV file and save it as XLS and remember that all " ; " starting with new column in XLS

Please help.

Best Regards
L

 

[SkipVought]

hi,

Do you not have the Excel application available?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Leosy]

I have excel.
But I'm receiving CSV file from client... make some changes with it and he needs XLS send back... ;/
I need to automate this.

So I need to convert csv file like this in to XSL ... ;/

 

[SkipVought]

IMPORT into Excel using Data > Get external Data > IMPORT TEXT.

It is possible to OPEN a .csv, but I would STRONGLY recommend against using the OPEN method, as you have no control over the process and Excel can and will CHANGE DATA in certain instances, in the eopen process.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Leosy]

I can't do it
I need vbscript do it for me... I need convert csv in to xls via vbs

is it possible ?
Some function ? etc ?

 

[Leosy]

I tried to develop new code.

Function ok()
set ce = CreateObject("Excel.Application")

newFileName="C:\BE\testnew.xls"
ce.Workbooks.Open "C:\BE\1.csv"
ce.ActiveWorkbook.SaveAs newFileName
ce.ActiveWorkbook.Close(0)
ce.Quit
Set ce = Nothing

end function

but no results. Nothing happening.

 

[MakeItSo]

I need vbscript do it for me... I need convert csv in to xls via vbs

No you don't! Why would you need vbs for such a relatively simple, STANDARD task?
Do as Skip recommends, that's fully sufficient!
This way you can even save the XLS as an updatable XLS which can re-query the data.

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[Leosy]

Because it need to be fully automated day by day. I can't touch it...

 

[MakeItSo]

Then you might want to go with VBA within an Excel file.
This is what the macro recorder gave me. Note the bold parts:

 With ActiveSheet[b].QueryTables.Add(Connection:= _
        "TEXT;C:\00_Projects_Temp\test.csv"[/b], Destination:=Range("$A$1"))
        .Name = "test"
        [b].FieldNames = True[/b]
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        [b].SaveData = True[/b]
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        [b].TextFileParseType = xlDelimited[/b]
        .TextFileTextQualifier = xlTextQualifierNone
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        [b].TextFileSemicolonDelimiter = True[/b]
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

That was done using the built-in "import external data" as by Skips advice.
This should help you get started.

Cheers,
MakeItSo

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[Leosy]

Thank you very much. I'll check this.

 

[Leosy]

I found some code

'======================================
' Convert CSV to XLS
'
' arg1: source - CSV path\file
' arg2: target - Excel path\file
'======================================

srccsvfile = Wscript.Arguments(0)  
tgtxlsfile = Wscript.Arguments(1)  

'Create Spreadsheet
'Look for an existing Excel instance.
On Error Resume Next ' Turn on the error handling flag
Set objExcel = GetObject(,"Excel.Application")
'If not found, create a new instance.
If Err.Number = 429 Then  '> 0
  Set objExcel = CreateObject("Excel.Application")
End If

objExcel.Visible = false
objExcel.displayalerts=false

'Import CSV into Spreadsheet
Set objWorkbook = objExcel.Workbooks.open(srccsvfile)
Set objWorksheet1 = objWorkbook.Worksheets(1)

'Adjust width of columns
Set objRange = objWorksheet1.UsedRange
objRange.EntireColumn.Autofit()
'This code could be used to AutoFit a select number of  columns
'For intColumns = 1 To 17
'    objExcel.Columns(intColumns).AutoFit()
'Next

'Make Headings Bold
objExcel.Rows(1).Font.Bold = TRUE

'Freeze header row
With objExcel.ActiveWindow
     .SplitColumn = 0
     .SplitRow = 1
End With
objExcel.ActiveWindow.FreezePanes = True

'Add Data Filters to Heading Row
objExcel.Rows(1).AutoFilter

'set header row gray
objExcel.Rows(1).Interior.ColorIndex = 15
'-0.249977111117893


'Save Spreadsheet, 51 = Excel 2007-2010 
objWorksheet1.SaveAs tgtxlsfile, 51

'Release Lock on Spreadsheet
objExcel.Quit()
Set objWorksheet1 = Nothing
Set objWorkbook = Nothing
Set ObjExcel = Nothing

but I have no idea how convert it to function, add path to csv and is it working as it should...

 

[SkipVought]

all this code is doing is OPENING the .csv file with Excel!!! see my post dated 8 May 12 9:51.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Leosy]

Ok so is it possible to open this semicolon separeted file with excel, than change data to be seperated in each column by " ; " and save as XLS ?

I have this code

OK

Function OK()
dim oEx
set oEx=Createobject("Excel.Application")
oEx.Workbooks.Open "C:\testing\in.csv"
oEx.ActiveWorkbook.SaveAs "C:\testing\out.xls", -4143, , , False, False
oEx.Quit

End Function

But it's only saving csv as xls witout changing data for each column...

 

[Leosy]

this VBA works fine

    Sub Importar()
    Dim Str1 As String
    Dim i As Integer
    Dim j As Long
    Dim Boo1 As Boolean
     
    With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = True
    .Filters.Clear
    .Filters.Add "Archivos Punto y Coma (*.csv)", "*.csv"
    .Filters.Add "Archivos Texto (*.txt)", "*.txt"
    .Filters.Add "Todos los Archivos (*.*)", "*.*"
    .Show
    If .SelectedItems.Count > 0 Then
    Worksheets(1).Activate
    j = 1
    For i = 1 To .SelectedItems.Count
        Str1 = "TEXT;" & .SelectedItems.Item(i)
        With ActiveSheet.QueryTables.Add(Connection:=Str1, Destination:=Cells(j, 1))
        .TextFileSemicolonDelimiter = True
        .Refresh BackgroundQuery:=False
        End With
        Boo1 = False
        While Boo1 = False
            j = j + 1
            If Cells(j, i).Value = "" Then
                Boo1 = True
            End If
        Wend
    Next
    End If
    End With
    End Sub

I wonder is it possible to implement this code in to this VBS code

OK

Function OK()
dim oEx
set oEx=Createobject("Excel.Application")
oEx.Workbooks.Open "C:\testing\in.csv"
oEx.ActiveWorkbook.SaveAs "C:\testing\out.xls", -4143, , , False, False
oEx.Quit

End Function

 

[Leosy]

Got it

I've converted it to FUNCTION and it's working

OK
Function OK()

Const xlDelimited = 1
Const xlNormal = -4143 

Dim Excel

Set Excel = CreateObject("Excel.Application")
With Excel
	.Workbooks.Open "C:\elle.csv"
	.Sheets(1).Columns("A").TextToColumns .Range("A1"), xlDelimited, , , , True  'semicolon-delimited
	.ActiveWorkbook.SaveAs .ActiveWorkbook.Path & "\1.xls", xlNormal
	.Quit
End With

End Function

 

[rtshort]

Very handy Leosy. Thanks for posting back your solution.


Rob
Just my $.02.

 

[SkipVought]

Some people never learn!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Leosy]

sorry SkipVought

I really didn't have time to sit and learn vbs as a PRO like you. I needed simple and fast solution to escape from my "problem". I'll back soon to vbs but maybe I'll have more time to learn this subject.

Thanks for you help and all suggestions. Have a nice day.