How to import cvs files into excel

[igmeou]

Hi,

Going through the forum but still couldn't find information how autoIT can import external file into excel.

Normally, I would do it in excel using Data->Import External Data->Import Data. Then I will browse for an cvs file and choose the appropriate delimiter and import that file into the excel sheet.

Is there any COM function or autoIT statement to automate these?

Thanks.

[Stumpii]

Try the following. As it is CSV, the delimiter is a comma.

$MyObject = ObjCreate("Excel.Application"); Create an Excel Object
If @error Then
    MsgBox(0, "Excel Error:", "Error creating Excel object!")
    Exit
EndIf
If Not IsObj($MyObject) Then
    MsgBox(16, "Excel Error:", "Error creating Excel object!")
    Exit
EndIf

$MyObject.Visible = 1

; UpdateLinks values
;0 Doesn't update any references 
;1 Updates external references but not remote references 
;2 Updates remote references but not external references 
;3 Updates both remote and external references 

; Formay values
;1 Tabs 
;2 Commas 
;3 Spaces 
;4 Semicolons 
;5 Nothing 
;6 Custom character (see the Delimiter argument) 

; exp[b][/b]ression.Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)
$MyObject.Workbooks.Open("c:\temp.csv", 0, false, 2)

$MyObject = 0

[seandisanti]

Hi,

Going through the forum but still couldn't find information how autoIT can import external file into excel.

Normally, I would do it in excel using Data->Import External Data->Import Data. Then I will browse for an cvs file and choose the appropriate delimiter and import that file into the excel sheet.

Is there any COM function or autoIT statement to automate these?

Thanks.

<{POST_SNAPBACK}>

are you trying to bring the data into an existing spreadsheet, or open it up into a new spreadsheet of it's own? if you're trying to just open it in it's own spreadsheet, you can just use a statement like:

Run("C:\Program Files\Microsoft Office\Office\excel.exe c:\blah.csv")

passing the filename as a parameter to the executable. if you're importing into an existing speadsheet that will involve more code but let me know and i'll write you something up

[igmeou]

Thanks. I tried it out and it give me an error like...

$MyObject.Workbooks.Open("c:\temp.csv", 0, false, 3)

$MyObject.Workbooks.Open("c:\temp.csv", 0, false, 3)^ ERROR

I'll be importing quite a number of files(csv) into a single excel file. Therefore, I need to know how to import can be done.

At least you gave me a direction to my problem. Thanks. But I don't know how to solve this error. I had tried adding ","s but it don't do the trick.

[seandisanti]

Thanks. I tried it out and it give me an error like...

$MyObject.Workbooks.Open("c:\temp.csv", 0, false, 3)

$MyObject.Workbooks.Open("c:\temp.csv", 0, false, 3)^ ERROR

I'll be importing quite a number of files(csv) into a single excel file. Therefore, I need to know how to import can be done.

At least you gave me a direction to my problem. Thanks. But I don't know how to solve this error. I had tried adding ","s but it don't do the trick.

<{POST_SNAPBACK}>

Sorry, i haven't taken the time to pick up the COM stuff yet, but if you like i'm sure i can make a vba script to do what you want.

[igmeou]

No problem. I would like to get my things going first while I may convert them to AutoIT later too.

PM me the vb script if possible.

[Mr. Crimson]

So you are taking several CVS files and trying to compile them into one worksheet? You could keep it as a CSV file as Excel typically can resolve what the delimiter is.

I might have a quick solution using that as the basis, you could easily loop this or add more files if necessary...

expandcollapse popup

#include <GUIConstants.au3>
#include <Array.au3>
#include <File.au3>

DIM $filename1
DIM $filename2
DIM $filename3
DIM $filecontents1
DIM $filecontents2
DIM $filecontents3
DIM $combinedfile
DIM $i
DIM $j
DIM $k

$i = 1
$j = 1
$k = 1

$filename1 = FileOpen("C:\temp\temp1.csv", 0)
$filename2 = FileOpen("C:\temp\temp2.csv", 0)
$filename3 = FileOpen("C:\temp\temp3.csv", 0)

Do
    $filecontents1 = $filecontents1 & @CRLF & FileReadLine($filename1, $i)
    $i = $i+1
Until @error = -1

Do
    $filecontents2 = $filecontents2 & @CRLF & FileReadLine($filename2, $j)
    $j = $j+1
Until @error = -1

Do
    $filecontents3 = $filecontents3 & @CRLF & FileReadLine($filename3, $k)
    $k = $k+1
Until @error = -1

FileClose($filename1)
FileClose($filename2)
FileClose($filename3)

$combinedfile = FileOpen("C:\temp\combinedfile.csv", 1)

FileWrite($combinedfile, $filecontents1 & @CRLF)
FileWrite($combinedfile, $filecontents2 & @CRLF)
FileWrite($combinedfile, $filecontents3 & @CRLF)

FileClose($combinedfile)

[igmeou]

I got some problem with this script.

As my this csv file is around 300MB. So I think it will take years to load into the memory...

There is no compile error or things like this, but I have been running it for half an hour on only 1 file and it still not yet done. So I don't think that can be a good solution for me to read the file into the memory.

Thanks anyway. I'm still in the middle of testing and finding better solutions. Can anyone help on this?

[seandisanti]

I got some problem with this script.

As my this csv file is around 300MB. So I think it will take years to load into the memory...

There is no compile error or things like this, but I have been running it for half an hour on only 1 file and it still not yet done. So I don't think that can be a good solution for me to read the file into the memory.

Thanks anyway. I'm still in the middle of testing and finding better solutions. Can anyone help on this?

<{POST_SNAPBACK}>

right now i'm working on some of my own reporting here at my job, then i'll whip up the vba solution for ya. i'm using office 2k, which office are you running?

[Mr. Crimson]

Egads! How many lines are in the file?

The upper limit for Excel reading files is pretty finite, at about 65,536 rows by 256 columns.

I'm not sure if a 300MB file would play well with Excel, does it open ok as is? I could be wrong, since it's not in the native XLS format, which could be the main cause of the limitation, but Excel may not be able to read files larger than the worksheet spec either... never tried.

Sounds like you might want to throw it into Access, but that depends on what you are doing.

Sorry I couldn't be more help!

-Crim

Edited September 9, 2005 by Mr. Crimson

[MHz]

The upper limit for Excel reading files is pretty finite, at about 65,536 rows by 256 columns.

<{POST_SNAPBACK}>

To my knowledge, Excel is only limited by memory as stated within it's manual. It has been a while since I read it last.

[Mr. Crimson]

(From the Excel helpfile)

Feature - Maximum limit

Open workbooks - Limited by available memory and system resources

Worksheet size - 65,536 rows by 256 columns

It's the worksheet size I'm concerned about, although it doesn't specify if that's in general, or only with XLS files.

I still could be wrong... I've been wrong frequently.

I personally have hit this limit before and had to move the project I was working on to an Access DB. We ended up with several million records, but as I recall it'd start to "crap" out at around 60,000 trying it in Excel, so this sort of confirms that, but again, it could be that it's a limit in the XLS file format, not Excel itself.

(Edit: I just tried to open a CVS file and go past the 65536th row, and it does appear to be an issue. This wouldn't preclude using CVS files, you'll just need to use Access and import it into a table)

-Crim

Edited September 9, 2005 by Mr. Crimson

[seandisanti]

To my knowledge, Excel is only limited by memory as stated within it's manual. It has been a while since I read it last.

<{POST_SNAPBACK}>

with office 2k, the line limit is 65,536 data beyond that can still be included as a data source, like for a pivot table etc, but only 65536 lines can be displayed at once. i'll install office xp real quick and see if that has the same limitation.

[Mr. Crimson]

I checked on Access, because it's relatively finite as well, considering you have "lots of files" and the first you tried was 300MB's.

Table size is limited to 2 gigs minus any other system objects in the DB you might have, so around 2 gigs.

(I've seen this limit too, strangely enough. hehe)

At that point, if you are above 2gb's of data, you might consider going to a SQL DB for storage of the data in an easily accessible way.

-Crim

[seandisanti]

with office 2k, the line limit is 65,536 data beyond that can still be included as a data source, like for a pivot table etc, but only 65536 lines can be displayed at once. i'll install office xp real quick and see if that has the same limitation.

<{POST_SNAPBACK}>

office xp has a limit of 65536 also. do you know how many lines you have in your csv files?

[igmeou]

Thanks all of your for the prompt reply.

I'm using Office 2k for this script and I had came across a msg saying it exceed 65535 rows to be imported but that is only when I try to "open" the csv file.

But if I do a proper import of an external file using the import external data tools, I have no issue with the rows limit. All my data will be imported fully.

[randallc]

Hi,

I am able to record an "import" macro in excel, then call it from AutoIt; I guess I could translate it, but hard work?

Best, Randall

Excel MacroRun

[randallc]

2mins for 380Mb file, though not tested beyond row numbers;

Here is macro recorded as

"ImportCSV"

in hidden "Personal.xls" sheet, started from startup directory of Excel

Sub ImportCSV()

'

' ImportCSV Macro

' Macro recorded 12/09/2005 by Randall Clapp

'

'

    With ActiveSheet.QueryTables.Add(Connection:= _

        "TEXT;C:\Program Files\AutoIt3\temp.csv", Destination:=Range( _

        "A1"))

        .Name = "fiftywideBy6"

        .FieldNames = True

        .RowNumbers = False

        .FillAdjacentFormulas = False

        .PreserveFormatting = True

        .RefreshOnFileOpen = False

        .RefreshStyle = xlInsertDeleteCells

        .SavePassword = False

        .SaveData = True

        .AdjustColumnWidth = True

        .RefreshPeriod = 0

        .TextFilePromptOnRefresh = False

        .TextFilePlatform = 437

        .TextFileStartRow = 1

        .TextFileParseType = xlDelimited

        .TextFileTextQualifier = xlTextQualifierDoubleQuote

        .TextFileConsecutiveDelimiter = False

        .TextFileTabDelimiter = True

        .TextFileSemicolonDelimiter = False

        .TextFileCommaDelimiter = True

        .TextFileSpaceDelimiter = False

        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _

        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)

        .TextFileTrailingMinusNumbers = True

        .Refresh BackgroundQuery:=False

    End With

    Application.CommandBars("External Data").Visible = False

    ChDir "C:\Program Files\AutoIt3"

    ActiveWorkbook.Save

    'Filename:="C:\Program Files\AutoIt3\book1.xls", _

        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _

        ReadOnlyRecommended:=False, CreateBackup:=False

End Sub

Here is calling script;

#include<ExcelCom.au3>

; none of these shorter commands are visible until you say "_XLshow"

dim $FilePath,$var1,$LastRow

$FilePath="c:\program files\autoit3\book1.xls"

If Not FileExists($FilePath) Or Not StringInStr($FilePath, "xls") Then

  $FilePath = FileOpenDialog("Go - Choose your excel file as inbuilt one not exists", $FilePath, "Worksheet" & " (" & "*.xls" & ")", 1)

EndIf

$Time1=@HOUR&" hour:"&@MIN&" @MIN:"&@sec&" @sec:"

_XLmacroRun($FilePath,1,"persoNAL.XLS!ImportCSV")

_XLshow($FilePath,1)

$Time2=@HOUR&" hour2:"&@MIN&" @MIN2:"&@sec&" @sec2:"

MsgBox(0,"_XLmacroRun=","$Time1="&$Time1&@CRLF&"$Time2="&$Time2)

Will this work for you?

[seandisanti]

Thanks all of your for the prompt reply.

I'm using Office 2k for this script and I had came across a msg saying it exceed 65535 rows to be imported but that is only when I try to "open" the csv file.

But if I do a proper import of an external file using the import external data tools, I have no issue with the rows limit. All my data will be imported fully.

<{POST_SNAPBACK}>

i think you should verify your data. even if it's not giving you an error, excel won't display greater than 65536 rows. do your import and confirm the last row displayed vs the last row imported, i am pretty sure they're not going to match...showing some data loss despite the lack of an error message.

[igmeou]

i think you should verify your data. even if it's not giving you an error, excel won't display greater than 65536 rows. do your import and confirm the last row displayed vs the last row imported, i am pretty sure they're not going to match...showing some data loss despite the lack of an error message.

You're right. I tried with my home system which is Excel 2003 it can't display any data greater than 65535 rows. I think I had mistaken as there isn't any error message pop-up for that. :">

Now I'm trying to figure out how to change that macro and script to suit my path and filename to test. Thanks randallc.