Sign in to follow this  
Followers 0
Dizzy

How to import a csv into excel

12 posts in this topic

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

post-83-12687510799179_thumb.jpg

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by omikron48

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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 by Valuater

NEWHeader1.png

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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 by picaxe

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

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 by 99ojo

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0