Sign in to follow this  
Followers 0
igmeou

How to import cvs files into excel

35 posts in this topic

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.


[font="Arial"]Thanks[/font]
If @error = me Then $sorry
Else
   Do
      $clarifyMe
   Until $meClear
EndIF
MsgBox(0,"Special Message!","Special Thanks to " & $allHadReplied,$Forever)

Share this post


Link to post
Share on other sites



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

“Give a man a script; you have helped him for today. Teach a man to script; and you will not have to hear him whine for help.”AutoIt4UE - Custom AutoIt toolbar and wordfile for UltraEdit/UEStudio users.AutoIt Graphical Debugger - A graphical debugger for AutoIt.SimMetrics COM Wrapper - Calculate string similarity.

Share this post


Link to post
Share on other sites

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


1100111 00001011101111 00011101101111 00010111100100 00001111110100 00110111110010 00101101111001 0011100i didn't make up this form of encryption, but i like it.credit to the lvl 6 challenge on arcanum.co.nz

Share this post


Link to post
Share on other sites

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.


[font="Arial"]Thanks[/font]
If @error = me Then $sorry
Else
   Do
      $clarifyMe
   Until $meClear
EndIF
MsgBox(0,"Special Message!","Special Thanks to " & $allHadReplied,$Forever)

Share this post


Link to post
Share on other sites

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.

1100111 00001011101111 00011101101111 00010111100100 00001111110100 00110111110010 00101101111001 0011100i didn't make up this form of encryption, but i like it.credit to the lvl 6 challenge on arcanum.co.nz

Share this post


Link to post
Share on other sites

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.


[font="Arial"]Thanks[/font]
If @error = me Then $sorry
Else
   Do
      $clarifyMe
   Until $meClear
EndIF
MsgBox(0,"Special Message!","Special Thanks to " & $allHadReplied,$Forever)

Share this post


Link to post
Share on other sites

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...

#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)

Share this post


Link to post
Share on other sites

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?


[font="Arial"]Thanks[/font]
If @error = me Then $sorry
Else
   Do
      $clarifyMe
   Until $meClear
EndIF
MsgBox(0,"Special Message!","Special Thanks to " & $allHadReplied,$Forever)

Share this post


Link to post
Share on other sites

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?

1100111 00001011101111 00011101101111 00010111100100 00001111110100 00110111110010 00101101111001 0011100i didn't make up this form of encryption, but i like it.credit to the lvl 6 challenge on arcanum.co.nz

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

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 by Mr. Crimson

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#12 ·  Posted (edited)

(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 by Mr. Crimson

Share this post


Link to post
Share on other sites

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.

1100111 00001011101111 00011101101111 00010111100100 00001111110100 00110111110010 00101101111001 0011100i didn't make up this form of encryption, but i like it.credit to the lvl 6 challenge on arcanum.co.nz

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

1100111 00001011101111 00011101101111 00010111100100 00001111110100 00110111110010 00101101111001 0011100i didn't make up this form of encryption, but i like it.credit to the lvl 6 challenge on arcanum.co.nz

Share this post


Link to post
Share on other sites

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.


[font="Arial"]Thanks[/font]
If @error = me Then $sorry
Else
   Do
      $clarifyMe
   Until $meClear
EndIF
MsgBox(0,"Special Message!","Special Thanks to " & $allHadReplied,$Forever)

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

1100111 00001011101111 00011101101111 00010111100100 00001111110100 00110111110010 00101101111001 0011100i didn't make up this form of encryption, but i like it.credit to the lvl 6 challenge on arcanum.co.nz

Share this post


Link to post
Share on other sites

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.


[font="Arial"]Thanks[/font]
If @error = me Then $sorry
Else
   Do
      $clarifyMe
   Until $meClear
EndIF
MsgBox(0,"Special Message!","Special Thanks to " & $allHadReplied,$Forever)

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