How to import a csv into excel

[Dizzy]

Hi,

i've a problem with importing csv-files into excel.

Based on a excel problem importing csv-files with dates, i

want to read the file in a "wizard-style".

For example:

(Save this as Boot.csv)

Computername; LastBootUpTime

Joe;02/11/2010 09:47:57;26,785371

Joe;02/12/2010 08:52:33;23,602951

Joe;02/12/2010 09:11:17;42,806674

Joe;02/15/2010 08:08:30;38,220245

Joe;02/15/2010 15:48:44;24,97576

Joe;02/24/2010 08:12:34;25,677764

Joe;02/25/2010 08:40:50;25,459363

Joe;02/26/2010 08:04:06;25,630964

Joe;03/01/2010 09:17:51;26,270568

Joe;03/02/2010 09:24:01;25,615364

Joe;03/12/2010 10:32:53;27,799378

Joe;03/12/2010 11:48:05;26,816572

Joe;03/15/2010 08:47:35;27,019373

Joe;03/16/2010 08:14:03;31,090999

Joe;03/16/2010 09:21:52;24,92896

Hopefully you cann see what i see (have a look to the picture).

OK - in this case, i want to import the file.

Manually this would be done in excel : (DATA) / From Text (select the file and "Import)

Use Semicolon in step 2 as delimiter and in step 3 select for the 2'nd column the format "Text".

Now i'm getting the original values.

How can i do this in AutoIt?

Thanks for reply

Dizzy

[BrettF]

Quick and dirty example.

#include <Excel.au3>
Dim $x = 1
Dim $y = 1
$sFile = "boot.csv"

$sExcel = "Excel.xls"

$read = FileRead($sFile)

Local $oExcel = _ExcelBookNew() ;Create new book, make it visible

$sArray = StringSplit($read, @CRLF)
For $i = 1 To $sArray[0]
    If $sArray[$i] = "" Then ContinueLoop
    $aTemp = StringSplit($sArray[$i], ";")
    For $a = 1 To $aTemp[0]
        _ExcelWriteCell($oExcel, $aTemp[$a], $y, $x) ;Write to the Cell
        $x += 1
    Next
    $y += 1
    $x = 1
Next

MsgBox(0, "Exiting", "Press OK to Save File and Exit")

_ExcelBookSaveAs($oExcel, @ScriptDir & $sExcel, "xls", 0, 1) ; Now we save it into the temp directory; overwrite existing file if necessary
_ExcelBookClose($oExcel) ; And finally we close out

Cheers,

Brett

[omikron48]

Doesn't Excel open CSV files and displays them as separate cells?

I don't think there's a need for any coding at all. Try opening your CSV file with Excel then saving it as XLS.

Edited March 17, 2010 by omikron48

[Valuater]

Doesn't Excel open CSV files and displays them as separate cells?

I don't think there's a need for any coding at all. Try opening your CSV file with Excel then saving it as XLS.

I'm thinking similar here..

8)

EDIT: not to take away from Brett's good code... 8)

Edited March 17, 2010 by Valuater

[BrettF]

Well it does:

#include <Excel.au3>

$sFilePath1 = @ScriptDir & "\boot.csv"
$sExcel = "Excel.xls"
$oExcel = _ExcelBookOpen($sFilePath1)

If @error = 1 Then
    MsgBox(0, "Error!", "Unable to Create the Excel Object")
    Exit
ElseIf @error = 2 Then
    MsgBox(0, "Error!", "File does not exist - Shame on you!")
    Exit
EndIf

_ExcelBookSaveAs($oExcel, @ScriptDir & "\" & $sExcel, "xls", 0, 1) ; Now we save it into the temp directory; overwrite existing file if necessary
_ExcelBookClose($oExcel) ; And finally we close out

But it splits by , and not ;. Slight problem there. Unless there is a way to change what the deliminator is when opening it?

[picaxe]

Another way, with semicolon delimiter

$sPathFile = @ScriptDir & "\test.tmp"
$sTxt = 'Computername;LastBootUpTime' & @CRLF & _
        'Joe;02/11/2010 09:47:57;26,785371' & @CRLF & _
        'Joe;02/12/2010 08:52:33;23,602951' & @CRLF & _
        'Joe;02/12/2010 09:11:17;42,806674' & @CRLF & _
        'Joe;03/15/2010 08:08:30;38,220245' & @CRLF & _
        'Joe;03/15/2010 15:48:44;24,97576'
If FileExists($sPathFile) Then FileDelete($sPathFile)
FileWrite($sPathFile, $sTxt)

;$sPathFile = @ScriptDir & "\Boot.csv"

$oExcel = ObjCreate("Excel.Application")
If IsObj($oExcel) Then
    $oExcel.Visible = 1
    ; .OpenText(FileName, Origin, StartRow, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo, TextVisualLayout, DecimalSeparator, ThousandsSeparator, TrailingMinusNumbers, Local)
    $oExcel.Workbooks.OpenText($sPathFile, 2, 1, 1, 1, False, False, True)
    $sWorksheet = StringRegExpReplace(StringRegExpReplace($sPathFile, '([\\ \w:]*)\\', ''), '\..*', '')
    With $oExcel.Worksheets($sWorksheet)
        .Columns("A:Z").AutoFit
        .Columns("B:B").NumberFormat = "dd/mm/yyyy hh:mm:ss"
        .Columns("B:B").HorizontalAlignment = -4131 ; xlLeft
    EndWith
EndIf

Edited March 17, 2010 by picaxe

[99ojo]

Hi,

But it splits by , and not ;. Slight problem there. Unless there is a way to change what the deliminator is when opening it?

Excel2000 splits by ',' and Excel2003 and above by ';'. Consequent behaviour, as csv -> Comma Separated Values ........

;-))

Stefan

@Edit: Found the regkey:

[HKEY_CURRENT_USER\Control Panel\International]

"sDecimal"=","

"sList"=";" <- This one is responsible

"sThousand"="." Upps, doesn't work...

Edited March 17, 2010 by 99ojo

[omikron48]

You can always do text replacement with notepad or notepad++. Replace ',' with ';' or vice-versa.

[99ojo]

Hi,

on german system with Excel2000, replacement could be worse, e.g:

Muster, Hans; Musterstrasse 10; 10111 Musterstadt

would generate an extra column by replacements. This is only a short problematic example......

On german system you have to change file assignments as follows to accept ';' as separator:

Explorer -> Tools -> Folder Options -> File Types -> CSV -> Advanced -> Select open then press Edit -> Use DDE should be checked ->

Change DDE Message from [open("%1")] to [öffnen("%1")].

After doing the changes, Excel2000 accepts ';' as separator. I don't know if this is only a problem with german systems.

;-))

Stefan

[PhilHibbs]

I think your dates are messed up because Excel thinks they are in a different format - 02/11/2010 is being interpreted as 2nd November, and 02/15/2010 is being read as a string as it is not a valid DD/MM/YYYY date.

[omikron48]

The dates aren't messed up. It just uses MM/DD/YYYY format. You can change your date formats using Regional Settings.

[PhilHibbs]

The dates aren't messed up. It just uses MM/DD/YYYY format. You can change your date formats using Regional Settings.

Well, they get messed up after importing. And you don't need to change your regional settings, you can set the date format of a column while importing, so your file could have different columns with different formats and that can be ok.