|
Home > Archive > dBASE Windows API > December 2005 > Imoorting a Text File into Excel
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Imoorting a Text File into Excel
|
|
|
| Is there a way thru OLE to import a textfile into Excel. I can easly do it within Excel and record a macro but when I copy to code into mdbase and make a few changes i get errors.
| |
| David Stone 2005-12-02, 3:23 am |
| Kevin wrote:
> Is there a way thru OLE to import a textfile into Excel. I can easly do it within Excel and record a macro but when I copy to code into mdbase and make a few changes i get errors.
What sort of textfile--- .csv? Do you need to specify the separator and other specs that Excel asks about when you do the import? If you can post a short section of the textfile and
the macro that you recorded (or just a description of what needs specifying), it would help.
David
| |
| Kevin J Cashmore 2005-12-02, 8:24 pm |
| David
I have used the OLE in many instances but only with a dBase file doing line by line and it has proved to be pertty agravating with long files. I the deceided to try other options. It is a text file. I just cant seam to find the right way to convert the M
acro to work with dBase. It works great with excel.... See below a snipit of the text file and the macro created in excel.
999 201-332- 0107 04791 SYRUS A 1
MARINE TERMINAL PORT NEWARK/ELIZABETH MARINE TERMINAL PORT EWR - ON CORBIN ST JERSEY BLVD. BAYONNE AUTO MARINE TERM-1 NJ ATT 05/26/1999 / / / /
999 201-332- 0153 04792 SYRUS A 1
MARINE TERMINAL PORT NEWARK/ELIZABETH MARINE TERMINAL PORT EWR - ON CORBIN ST JERSEY BLVD. BAYONNE AUTO MARINE TERM-2 NJ ATT 05/26/1999 / / / /
999 201-332-9653 04542 SERIES 5 A 1 PATH STATIONS IN NJ GROVE STREET STATION GROVE STREET STATION NEAR NEWS STAND-2 NJ ATT 03/02/1999 / / / /
Sub Import_Statdump()
'
' Import_Statdump Macro
' Macro recorded 12/2/2005 by kjcashmore
'
'
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:=" TEXT;C:\Wintemp\stat
dump.txt", _
Destination:=Range("A1"))
.Name = "statdump"
.FieldNames = True
.RowNumbers = False
. FillAdjacentFormulas
= False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
. TextFilePromptOnRefr
esh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
. TextFileTextQualifie
r = xlTextQualifierDoubl
eQuote
. TextFileConsecutiveD
elimiter = False
. TextFileTabDelimiter
= True
. TextFileSemicolonDel
imiter = False
. TextFileCommaDelimit
er = False
. TextFileSpaceDelimit
er = False
. TextFileColumnDataTy
pes = Array(1, 1, 1, 1)
. TextFileTrailingMinu
sNumbers = True
.Refresh BackgroundQuery:=Fal
se
End With
End Sub
Thanks for your help.
David Stone Wrote:
> Kevin wrote:
>
>
> What sort of textfile--- .csv? Do you need to specify the separator and other specs that Excel asks about when you do the import? If you can post a short section of the textfile and
> the macro that you recorded (or just a description of what needs specifying), it would help.
>
> David
>
| |
| David Stone 2005-12-02, 8:24 pm |
| Kevin,
Can you generate/receive these files in comma-separated format? I think it would simplify importing into Excel. Also, the 3rd record doesn't seem to match the format of the first 2; would also be easier to deal with these sorts of inconsistencies if the d
ata were comma-separated.
David
| |
| Ivar B. Jessen 2005-12-02, 8:24 pm |
| On Fri, 02 Dec 2005 12:10:01 -0800, David Stone <dlstone@wholegrain.com> wrote:
>Can you generate/receive these files in comma-separated format? I think it would simplify importing into Excel. Also, the 3rd record doesn't seem to match the format of the first 2; would also be easier to deal with these sorts of inconsistencies if the
data were comma-separated.
Looking at in a hex reader it appears to be Tab separated Each record has 14
Tabs corresponding to 15 fields. Below is the same file with chr(182) instead of
chr(9).
Ivar B. Jessen
999¶201-332- 0107¶04791¶SYRUS¶A¶1
¶MARINE TERMINAL PORT NEWARK/ELIZABETH¶MARINE
TERMINAL PORT EWR - ON CORBIN ST¶ JERSEY BLVD. BAYONNE¶AUTO MARINE
TERM-1¶NJ¶ATT¶05/26/1999¶ / /¶ / /
999¶201-332- 0153¶04792¶SYRUS¶A¶1
¶MARINE TERMINAL PORT NEWARK/ELIZABETH¶MARINE
TERMINAL PORT EWR - ON CORBIN ST¶ JERSEY BLVD. BAYONNE¶AUTO MARINE
TERM-2¶NJ¶ATT¶05/26/1999¶ / /¶ / /
999¶201-332-9653¶04542¶SERIES 5¶A¶1¶PATH STATIONS IN NJ¶GROVE STREET
STATION¶GROVE STREET STATION¶NEAR NEWS STAND-2¶NJ¶ATT¶03/02/1999¶ / /¶ / /
| |
| Kevin J Cashmore 2005-12-02, 8:24 pm |
| Let me explain.... This is only a piece of the file and it imports properly in excel.....The problem is making the macro that i created in Excel to work as dBase code opening it with the code below
********************
********************
**********
oExcel := new oleAutoclient("excel.application")
oExcel.workbooks.add()
//Rem this statement if you don't want to watch excel at work
oExcel.visible = true
//Now add the code to import the text file
********************
********************
***********
Ivar B. Jessen Wrote:
> On Fri, 02 Dec 2005 12:10:01 -0800, David Stone <dlstone@wholegrain.com> wrote:
>
e data were comma-separated.[color=darkred]
>
> Looking at in a hex reader it appears to be Tab separated Each record has 14
> Tabs corresponding to 15 fields. Below is the same file with chr(182) instead of
> chr(9).
>
>
> Ivar B. Jessen
>
>
>
> 999¶201-332- 0107¶04791¶SYRUS¶A¶1
¶MARINE TERMINAL PORT NEWARK/ELIZABETH¶MARINE
> TERMINAL PORT EWR - ON CORBIN ST¶ JERSEY BLVD. BAYONNE¶AUTO MARINE
> TERM-1¶NJ¶ATT¶05/26/1999¶ / /¶ / /
> 999¶201-332- 0153¶04792¶SYRUS¶A¶1
¶MARINE TERMINAL PORT NEWARK/ELIZABETH¶MARINE
> TERMINAL PORT EWR - ON CORBIN ST¶ JERSEY BLVD. BAYONNE¶AUTO MARINE
> TERM-2¶NJ¶ATT¶05/26/1999¶ / /¶ / /
> 999¶201-332-9653¶04542¶SERIES 5¶A¶1¶PATH STATIONS IN NJ¶GROVE STREET
> STATION¶GROVE STREET STATION¶NEAR NEWS STAND-2¶NJ¶ATT¶03/02/1999¶ / /¶ / /
| |
|
|
Kevin J Cashmore wrote:
> Let me explain.... This is only a piece of the file and it imports properly in excel.....The problem is making the macro that i created in Excel to work as dBase code opening it with the code below
Hi Kevin, I understand---I was just thinking that if the data is easily
obtainable by you in a very standard format (csv) the OLE import can be
done (I think) much more easily. Before going to the trouble of doing it
the harder way, I wanted to know if it might be done the easy way. So,
can these data files be created in comma-separated format?
David
| |
| David Stone 2005-12-03, 3:23 am |
| Kevin, try this:
Save the following (making sure that each line is intact and not wrapped) to statdump.csv in your current dBASE directory:
999,201-332- 0107,04791,SYRUS,A,1
,MARINE TERMINAL PORT NEWARK/ELIZABETH,MARINE TERMINAL PORT EWR - ON CORBIN ST, JERSEY BLVD. BAYONNE,AUTO MARINE TERM-1,NJ,ATT,05/26/1999, / /, / /
999,201-332- 0153,04792,SYRUS,A,1
,MARINE TERMINAL PORT NEWARK/ELIZABETH,MARINE TERMINAL PORT EWR - ON CORBIN ST, JERSEY BLVD. BAYONNE,AUTO MARINE TERM-2,NJ,ATT,05/26/1999, / /, / /
999,201-332-9653,04542,SERIES 5,A,1,PATH STATIONS IN NJ,GROVE STREET STATION,GROVE STREET STATION,NEAR NEWS STAND-2,NJ,ATT,03/02/1999, / /, / /
The above is (thanks Ivar!) the csv version of Ivar's tab-replaced version of the piece of your data file.
Then save the following as oleimport.prg (or some such name) to the same directory:
oExcel = new oleautoclient('excel
.application')
oExcel.visible := true
cName = set('directory') + '\statdump.csv'
oExcel.workbooks.open(cName)
and run it.
But if csv format isn't possible, we'll do it the harder way...with just tabs separating, can't be that difficult.
David
| |
| kjcashmore 2005-12-03, 11:23 am |
| I can make it like that
DLS Wrote:
>
>
> Kevin J Cashmore wrote:
>
>
> Hi Kevin, I understand---I was just thinking that if the data is easily
> obtainable by you in a very standard format (csv) the OLE import can be
> done (I think) much more easily. Before going to the trouble of doing it
> the harder way, I wanted to know if it might be done the easy way. So,
> can these data files be created in comma-separated format?
>
> David
>
| |
| kjcashmore 2005-12-03, 11:23 am |
| Message-ID: <izpvezC#FHA.1520@news-server>
Newsgroups: dbase.dde-ole-dll-winapi
Lines: 32
Path: news-server
Xref: news-server dbase.dde-ole-dll-winapi:329
NNTP-Posting-Host: dbase.com 64.132.211.168
Thanks, I give it a try
David Stone Wrote:
> Kevin, try this:
>
> Save the following (making sure that each line is intact and not wrapped) to statdump.csv in your current dBASE directory:
>
> 999,201-332- 0107,04791,SYRUS,A,1
,MARINE TERMINAL PORT NEWARK/ELIZABETH,MARINE TERMINAL PORT EWR - ON CORBIN ST, JERSEY BLVD. BAYONNE,AUTO MARINE TERM-1,NJ,ATT,05/26/1999, / /, / /
> 999,201-332- 0153,04792,SYRUS,A,1
,MARINE TERMINAL PORT NEWARK/ELIZABETH,MARINE TERMINAL PORT EWR - ON CORBIN ST, JERSEY BLVD. BAYONNE,AUTO MARINE TERM-2,NJ,ATT,05/26/1999, / /, / /
> 999,201-332-9653,04542,SERIES 5,A,1,PATH STATIONS IN NJ,GROVE STREET STATION,GROVE STREET STATION,NEAR NEWS STAND-2,NJ,ATT,03/02/1999, / /, / /
>
> The above is (thanks Ivar!) the csv version of Ivar's tab-replaced version of the piece of your data file.
>
> Then save the following as oleimport.prg (or some such name) to the same directory:
>
> oExcel = new oleautoclient('excel
.application')
> oExcel.visible := true
> cName = set('directory') + '\statdump.csv'
> oExcel.workbooks.open(cName)
>
> and run it.
>
> But if csv format isn't possible, we'll do it the harder way...with just tabs separating, can't be that difficult.
>
> David
>
| |
| David Stone 2005-12-03, 1:23 pm |
| kjcashmore wrote:
> I can make it like that
Then the above-posted code should do the trick.
David
| |
| Ivar B. Jessen 2005-12-03, 1:23 pm |
| X-Newsreader: Forte Free Agent 2.0/32.652
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: dbase.dde-ole-dll-winapi
Lines: 18
Path: news-server
Xref: news-server dbase.dde-ole-dll-winapi:331
NNTP-Posting-Host: port1076.uc2-oers.ppp.cybercity.dk 217.157.115.191
On Fri, 02 Dec 2005 23:10:20 -0800, David Stone <dlstone@wholegrain.com> wrote:
>Then save the following as oleimport.prg (or some such name) to the same directory:
>
>oExcel = new oleautoclient('excel
.application')
>oExcel.visible := true
>cName = set('directory') + '\statdump.csv'
>oExcel.workbooks.open(cName)
>
>and run it.
>
>But if csv format isn't possible, we'll do it the harder way...with just tabs separating, can't be that difficult.
David,
I saved the original Tab-separated records as statdump.txt and when running your
code the data was imported as fifteen columns and three rows in Excel.
The 'harder' way appears to be the easier way :-)
Ivar B. Jessen
| |
| David Stone 2005-12-03, 8:23 pm |
| "Ivar B. Jessen" wrote:
> I saved the original Tab-separated records as statdump.txt and when running your
> code the data was imported as fifteen columns and three rows in Excel.
Hi Ivar,
Well I'll be durned....must be the new smarter Excel you're using, because Excel on this machine ('97) doesn't do
that--dumps it all into column A. I bet your Excel will also correctly paste an html table into appropriate
cells----'97 won't do that either.
Hey Kevin, before you bother with csv format, try importing the file as-is.
David
| |
| KJCashmore 2005-12-03, 8:23 pm |
| I can import many different types i.e. txt, dbf .... with no problem with in excel and thru OLE... BTW I am running Office XP and dBase build 1884 Thanks
David Stone Wrote:
> "Ivar B. Jessen" wrote:
>
>
> Hi Ivar,
>
> Well I'll be durned....must be the new smarter Excel you're using, because Excel on this machine ('97) doesn't do
> that--dumps it all into column A. I bet your Excel will also correctly paste an html table into appropriate
> cells----'97 won't do that either.
>
> Hey Kevin, before you bother with csv format, try importing the file as-is.
>
> David
>
| |
| David Stone 2005-12-04, 3:23 am |
| KJCashmore wrote:
> I can import many different types i.e. txt, dbf .... with no problem with in excel and thru OLE... BTW I am running Office XP and dBase build 1884 Thanks
Kevin, what I meant was to try using the OLE code I posted but
substitute 'statdump.txt' (or whatever your original data file is
called) in place of 'statdump.csv' (the comma-separated data chunk) and
see if the tab-separated data is imported.
David
|
|
|
|
|