[archive] OpenText in Excel

[Migrated content. Thread originally posted on 15 April 2005]

Hello,

i have to import nearly 15.000 data-records into excel from serveral vision-files.

first test:
a direct import into excel
result:
the programm runs 5 hours and then crash with error "not enough memory".

second test:
direct import into excel and save Workbook every 500 data-records (hope that less memory is needed)
result:
same as first test.

third test:
import to a text-file, open it with excel
Problem:
text-lines with "=" at beginning
Solution:
define the Columns as "Text"

So i created a macro in Excel to see how i have to write it:

    Workbooks.OpenText Filename:="C:\\muffrohr\\SEQ-ART.TXT", Origin:=xlMSDOS, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
        Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2), _
        Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array( _
        16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1)) _
        , TrailingMinusNumbers:=True


And here is my Problem:

how must i write in Cobol the FieldInfo?

Without FieldInfo i can open the TextFile:

           modify xls-app WORKBOOKS::OpenText(
                          by name FileName "c:\\muffrohr\\seq-art.txt",
                          by name Origin               xlMSDOS,
                          by name StartRow             1,
                          by name DataType             xlDelimited,
                          by name TextQualifier        xlNone,
                          by name ConsecutiveDelimiter 0,
                          by name Tab                  0,
                          by name Semicolon            1,
                          by name Comma                0,
                          by name Space                0,
                          by name Other                0,
                          by name TrailingMinusNumbers 1
                                             )
                  giving xls-book.


FieldInfo i have no Idea how to write it...

           modify xls-app WORKBOOKS::OpenText(
                          by name FileName "c:\\muffrohr\\seq-art.txt",
                          by name Origin               xlMSDOS,
                          by name StartRow             1,
                          by name DataType             xlDelimited,
                          by name TextQualifier        xlNone,
                          by name ConsecutiveDelimiter 0,
                          by name Tab                  0,
                          by name Semicolon            1,
                          by name Comma                0,
                          by name Space                0,
                          by name Other                0,
                          by name FieldInfo            array(1, 1),
                                                       array(2, 1),
                                                       array(3, 1),
                                                       array(4, 1),
                                                       array(5, 1),
                                                       array(6, 2),
                                                       array(7, 2),
                                                       array(8, 2),
                                                       array(9, 2),
                                                       array(10, 2),
                                                       array(11, 2),
                                                       array(12, 1),
                                                       array(13, 1),
                                                       array(14, 2),
                                                       array(15, 1),
                                                       array(16, 2),
                                                       array(17, 1),
                                                       array(18, 2),
                                                       array(19, 1),
                                                       array(20, 2),
                                                       array(21, 1),
                                                       array(22, 1),
                          by name TrailingMinusNumbers 1
                                             )
                  giving xls-book.


array is unknow, without array it don't work, tested several variant with

call "C$SETVARIANT" using "...", h-fieldinfo.

But nothing worked :(

Any have an idea how the correct syntax is?

Regards
David Neidinger

PS: sorry for my bad english

Here is what Microsoft documentations says about it. I notice it says it is optional. I would try to run without it if I were you.

FieldInfo Optional Variant. An array containing parse information for individual columns of data. The interpretation depends on the value of DataType.

When the data is delimited, this argument is an array of two-element arrays, with each two-element array specifying the conversion options for a particular column. The first element is the column number (1-based), and the second element is one of the following numbers, specifying how the column in parsed.

1
General

2
Text




(continued)

3
MDY date

4
DMY date

5
YMD date

6
MYD date

7
DYM date

8
YDM date

9
Skip the column




The column specifiers can be in any order. If there's no column specifier for a particular column in the input data, the column is parsed with the General setting. This example causes the third column to be skipped, the first column to be parsed as text, and the remaining columns in the source data to be parsed with the General setting.

Array(Array(3, 9), Array(1, 2))
If the source data has fixed-width columns, the first element in each two-element array specifies the position of the starting character in the column (as an integer; character 0 (zero) is the first character). The second element in the two-element array specifies the parse option for the column as a number between 1 and 9, as listed in the preceding table.

The following example parses two columns from a fixed-width text file. The first column includes characters 1 through 10. Characters 11, 12, 13, 14, and 15 are skipped. The second column includes character 16 through the last character in the line.

Array(Array(0, 1), Array(10, 9), Array(15, 1))

[Migrated content. Thread originally posted on 15 April 2005]

Hello,

i have to import nearly 15.000 data-records into excel from serveral vision-files.

first test:
a direct import into excel
result:
the programm runs 5 hours and then crash with error "not enough memory".

second test:
direct import into excel and save Workbook every 500 data-records (hope that less memory is needed)
result:
same as first test.

third test:
import to a text-file, open it with excel
Problem:
text-lines with "=" at beginning
Solution:
define the Columns as "Text"

So i created a macro in Excel to see how i have to write it:

    Workbooks.OpenText Filename:="C:\\muffrohr\\SEQ-ART.TXT", Origin:=xlMSDOS, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
        Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2), _
        Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array( _
        16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1)) _
        , TrailingMinusNumbers:=True


And here is my Problem:

how must i write in Cobol the FieldInfo?

Without FieldInfo i can open the TextFile:

           modify xls-app WORKBOOKS::OpenText(
                          by name FileName "c:\\muffrohr\\seq-art.txt",
                          by name Origin               xlMSDOS,
                          by name StartRow             1,
                          by name DataType             xlDelimited,
                          by name TextQualifier        xlNone,
                          by name ConsecutiveDelimiter 0,
                          by name Tab                  0,
                          by name Semicolon            1,
                          by name Comma                0,
                          by name Space                0,
                          by name Other                0,
                          by name TrailingMinusNumbers 1
                                             )
                  giving xls-book.


FieldInfo i have no Idea how to write it...

           modify xls-app WORKBOOKS::OpenText(
                          by name FileName "c:\\muffrohr\\seq-art.txt",
                          by name Origin               xlMSDOS,
                          by name StartRow             1,
                          by name DataType             xlDelimited,
                          by name TextQualifier        xlNone,
                          by name ConsecutiveDelimiter 0,
                          by name Tab                  0,
                          by name Semicolon            1,
                          by name Comma                0,
                          by name Space                0,
                          by name Other                0,
                          by name FieldInfo            array(1, 1),
                                                       array(2, 1),
                                                       array(3, 1),
                                                       array(4, 1),
                                                       array(5, 1),
                                                       array(6, 2),
                                                       array(7, 2),
                                                       array(8, 2),
                                                       array(9, 2),
                                                       array(10, 2),
                                                       array(11, 2),
                                                       array(12, 1),
                                                       array(13, 1),
                                                       array(14, 2),
                                                       array(15, 1),
                                                       array(16, 2),
                                                       array(17, 1),
                                                       array(18, 2),
                                                       array(19, 1),
                                                       array(20, 2),
                                                       array(21, 1),
                                                       array(22, 1),
                          by name TrailingMinusNumbers 1
                                             )
                  giving xls-book.


array is unknow, without array it don't work, tested several variant with

call "C$SETVARIANT" using "...", h-fieldinfo.

But nothing worked :(

Any have an idea how the correct syntax is?

Regards
David Neidinger

PS: sorry for my bad english

Here is what Microsoft documentations says about it. I notice it says it is optional. I would try to run without it if I were you.

FieldInfo Optional Variant. An array containing parse information for individual columns of data. The interpretation depends on the value of DataType.

When the data is delimited, this argument is an array of two-element arrays, with each two-element array specifying the conversion options for a particular column. The first element is the column number (1-based), and the second element is one of the following numbers, specifying how the column in parsed.

1
General

2
Text




(continued)

3
MDY date

4
DMY date

5
YMD date

6
MYD date

7
DYM date

8
YDM date

9
Skip the column




The column specifiers can be in any order. If there's no column specifier for a particular column in the input data, the column is parsed with the General setting. This example causes the third column to be skipped, the first column to be parsed as text, and the remaining columns in the source data to be parsed with the General setting.

Array(Array(3, 9), Array(1, 2))
If the source data has fixed-width columns, the first element in each two-element array specifies the position of the starting character in the column (as an integer; character 0 (zero) is the first character). The second element in the two-element array specifies the parse option for the column as a number between 1 and 9, as listed in the preceding table.

The following example parses two columns from a fixed-width text file. The first column includes characters 1 through 10. Characters 11, 12, 13, 14, and 15 are skipped. The second column includes character 16 through the last character in the line.

Array(Array(0, 1), Array(10, 9), Array(15, 1))

[Migrated content. Thread originally posted on 15 April 2005]

Hello,

i have to import nearly 15.000 data-records into excel from serveral vision-files.

first test:
a direct import into excel
result:
the programm runs 5 hours and then crash with error "not enough memory".

second test:
direct import into excel and save Workbook every 500 data-records (hope that less memory is needed)
result:
same as first test.

third test:
import to a text-file, open it with excel
Problem:
text-lines with "=" at beginning
Solution:
define the Columns as "Text"

So i created a macro in Excel to see how i have to write it:

    Workbooks.OpenText Filename:="C:\\muffrohr\\SEQ-ART.TXT", Origin:=xlMSDOS, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
        Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2), _
        Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array( _
        16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1)) _
        , TrailingMinusNumbers:=True


And here is my Problem:

how must i write in Cobol the FieldInfo?

Without FieldInfo i can open the TextFile:

           modify xls-app WORKBOOKS::OpenText(
                          by name FileName "c:\\muffrohr\\seq-art.txt",
                          by name Origin               xlMSDOS,
                          by name StartRow             1,
                          by name DataType             xlDelimited,
                          by name TextQualifier        xlNone,
                          by name ConsecutiveDelimiter 0,
                          by name Tab                  0,
                          by name Semicolon            1,
                          by name Comma                0,
                          by name Space                0,
                          by name Other                0,
                          by name TrailingMinusNumbers 1
                                             )
                  giving xls-book.


FieldInfo i have no Idea how to write it...

           modify xls-app WORKBOOKS::OpenText(
                          by name FileName "c:\\muffrohr\\seq-art.txt",
                          by name Origin               xlMSDOS,
                          by name StartRow             1,
                          by name DataType             xlDelimited,
                          by name TextQualifier        xlNone,
                          by name ConsecutiveDelimiter 0,
                          by name Tab                  0,
                          by name Semicolon            1,
                          by name Comma                0,
                          by name Space                0,
                          by name Other                0,
                          by name FieldInfo            array(1, 1),
                                                       array(2, 1),
                                                       array(3, 1),
                                                       array(4, 1),
                                                       array(5, 1),
                                                       array(6, 2),
                                                       array(7, 2),
                                                       array(8, 2),
                                                       array(9, 2),
                                                       array(10, 2),
                                                       array(11, 2),
                                                       array(12, 1),
                                                       array(13, 1),
                                                       array(14, 2),
                                                       array(15, 1),
                                                       array(16, 2),
                                                       array(17, 1),
                                                       array(18, 2),
                                                       array(19, 1),
                                                       array(20, 2),
                                                       array(21, 1),
                                                       array(22, 1),
                          by name TrailingMinusNumbers 1
                                             )
                  giving xls-book.


array is unknow, without array it don't work, tested several variant with

call "C$SETVARIANT" using "...", h-fieldinfo.

But nothing worked :(

Any have an idea how the correct syntax is?

Regards
David Neidinger

PS: sorry for my bad english

Thanks for the Info,

but i still figured out that 1 is General and 2 is Text and Text i need :)

when i import a text-field in excel which first sign is an "=" it is handled like a formula. So i have to declare this Field as a text-field - else in the Excel-Field is "#name" and not the imported text.

I only don't know how to define the arrays in cobol :)

If i write array in front of (1, 2)
the compiler say "array is inknown" :(

Tested several ways it could be written - but no one worked :confused:


At moment i test to import the date in a preformated excel-sheet - import works - but still i have the problem with "#name".

Regards
David

[Migrated content. Thread originally posted on 15 April 2005]

Hello,

i have to import nearly 15.000 data-records into excel from serveral vision-files.

first test:
a direct import into excel
result:
the programm runs 5 hours and then crash with error "not enough memory".

second test:
direct import into excel and save Workbook every 500 data-records (hope that less memory is needed)
result:
same as first test.

third test:
import to a text-file, open it with excel
Problem:
text-lines with "=" at beginning
Solution:
define the Columns as "Text"

So i created a macro in Excel to see how i have to write it:

    Workbooks.OpenText Filename:="C:\\muffrohr\\SEQ-ART.TXT", Origin:=xlMSDOS, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
        Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2), _
        Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array( _
        16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1)) _
        , TrailingMinusNumbers:=True


And here is my Problem:

how must i write in Cobol the FieldInfo?

Without FieldInfo i can open the TextFile:

           modify xls-app WORKBOOKS::OpenText(
                          by name FileName "c:\\muffrohr\\seq-art.txt",
                          by name Origin               xlMSDOS,
                          by name StartRow             1,
                          by name DataType             xlDelimited,
                          by name TextQualifier        xlNone,
                          by name ConsecutiveDelimiter 0,
                          by name Tab                  0,
                          by name Semicolon            1,
                          by name Comma                0,
                          by name Space                0,
                          by name Other                0,
                          by name TrailingMinusNumbers 1
                                             )
                  giving xls-book.


FieldInfo i have no Idea how to write it...

           modify xls-app WORKBOOKS::OpenText(
                          by name FileName "c:\\muffrohr\\seq-art.txt",
                          by name Origin               xlMSDOS,
                          by name StartRow             1,
                          by name DataType             xlDelimited,
                          by name TextQualifier        xlNone,
                          by name ConsecutiveDelimiter 0,
                          by name Tab                  0,
                          by name Semicolon            1,
                          by name Comma                0,
                          by name Space                0,
                          by name Other                0,
                          by name FieldInfo            array(1, 1),
                                                       array(2, 1),
                                                       array(3, 1),
                                                       array(4, 1),
                                                       array(5, 1),
                                                       array(6, 2),
                                                       array(7, 2),
                                                       array(8, 2),
                                                       array(9, 2),
                                                       array(10, 2),
                                                       array(11, 2),
                                                       array(12, 1),
                                                       array(13, 1),
                                                       array(14, 2),
                                                       array(15, 1),
                                                       array(16, 2),
                                                       array(17, 1),
                                                       array(18, 2),
                                                       array(19, 1),
                                                       array(20, 2),
                                                       array(21, 1),
                                                       array(22, 1),
                          by name TrailingMinusNumbers 1
                                             )
                  giving xls-book.


array is unknow, without array it don't work, tested several variant with

call "C$SETVARIANT" using "...", h-fieldinfo.

But nothing worked :(

Any have an idea how the correct syntax is?

Regards
David Neidinger

PS: sorry for my bad english

Here is an example of how to define a safearray in ACUCOBOL-GT:
code]
01 MyTable.
03 FILLER OCCURS 10.
05 MyItem USAGE HANDLE OF VARIANT OCCURS 2.
[/code]

Here is how to set it:

CALL "C$SETVARIANT" USING somevalue
         MyItem(I, J)


Note that somevalue can be anything, text, number... The runtime automatically adopts the correct variant.

Here is ONE example on how to use it:

MODIFY  oSheet Range("A1")::Resize(10 , 2)::Value =
               MyTable.


This was supposed to format nicely, but for some reason...

Your English is fine, don't worry about that.

[Migrated content. Thread originally posted on 15 April 2005]

Hello,

i have to import nearly 15.000 data-records into excel from serveral vision-files.

first test:
a direct import into excel
result:
the programm runs 5 hours and then crash with error "not enough memory".

second test:
direct import into excel and save Workbook every 500 data-records (hope that less memory is needed)
result:
same as first test.

third test:
import to a text-file, open it with excel
Problem:
text-lines with "=" at beginning
Solution:
define the Columns as "Text"

So i created a macro in Excel to see how i have to write it:

    Workbooks.OpenText Filename:="C:\\muffrohr\\SEQ-ART.TXT", Origin:=xlMSDOS, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
        Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2), _
        Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array( _
        16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1)) _
        , TrailingMinusNumbers:=True


And here is my Problem:

how must i write in Cobol the FieldInfo?

Without FieldInfo i can open the TextFile:

           modify xls-app WORKBOOKS::OpenText(
                          by name FileName "c:\\muffrohr\\seq-art.txt",
                          by name Origin               xlMSDOS,
                          by name StartRow             1,
                          by name DataType             xlDelimited,
                          by name TextQualifier        xlNone,
                          by name ConsecutiveDelimiter 0,
                          by name Tab                  0,
                          by name Semicolon            1,
                          by name Comma                0,
                          by name Space                0,
                          by name Other                0,
                          by name TrailingMinusNumbers 1
                                             )
                  giving xls-book.


FieldInfo i have no Idea how to write it...

           modify xls-app WORKBOOKS::OpenText(
                          by name FileName "c:\\muffrohr\\seq-art.txt",
                          by name Origin               xlMSDOS,
                          by name StartRow             1,
                          by name DataType             xlDelimited,
                          by name TextQualifier        xlNone,
                          by name ConsecutiveDelimiter 0,
                          by name Tab                  0,
                          by name Semicolon            1,
                          by name Comma                0,
                          by name Space                0,
                          by name Other                0,
                          by name FieldInfo            array(1, 1),
                                                       array(2, 1),
                                                       array(3, 1),
                                                       array(4, 1),
                                                       array(5, 1),
                                                       array(6, 2),
                                                       array(7, 2),
                                                       array(8, 2),
                                                       array(9, 2),
                                                       array(10, 2),
                                                       array(11, 2),
                                                       array(12, 1),
                                                       array(13, 1),
                                                       array(14, 2),
                                                       array(15, 1),
                                                       array(16, 2),
                                                       array(17, 1),
                                                       array(18, 2),
                                                       array(19, 1),
                                                       array(20, 2),
                                                       array(21, 1),
                                                       array(22, 1),
                          by name TrailingMinusNumbers 1
                                             )
                  giving xls-book.


array is unknow, without array it don't work, tested several variant with

call "C$SETVARIANT" using "...", h-fieldinfo.

But nothing worked :(

Any have an idea how the correct syntax is?

Regards
David Neidinger

PS: sorry for my bad english

thx for the input :)

i had also the problem that i can't modify anything after i open the csv with

modify xls-app WORKBOOKS::OpenText(....) giving xls-book.

for example:
inquire xls-book WORKSHEETS xls-sheets.
cause an error.

I tested to add another sheet - but also causes an error.

If i start a blank excel workbook with
modify xls-app WORKBOOKS::Add() giving xls-book.

all code works, which fails after Workbooks::OpenText :confused:

So i tested to import data like Kelli ( http://www.acucorp.com/support/supported/customer_forum/showthread.php?s=&threadid=481 )

and still workes fine :)

The array to define the columns in imported is a little bit different - so i have to figure out to port your sample code to it.

But i think i can handle it....

Thanks
David

[Migrated content. Thread originally posted on 15 April 2005]

Hello,

i have to import nearly 15.000 data-records into excel from serveral vision-files.

first test:
a direct import into excel
result:
the programm runs 5 hours and then crash with error "not enough memory".

second test:
direct import into excel and save Workbook every 500 data-records (hope that less memory is needed)
result:
same as first test.

third test:
import to a text-file, open it with excel
Problem:
text-lines with "=" at beginning
Solution:
define the Columns as "Text"

So i created a macro in Excel to see how i have to write it:

    Workbooks.OpenText Filename:="C:\\muffrohr\\SEQ-ART.TXT", Origin:=xlMSDOS, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
        Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2), _
        Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array( _
        16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1)) _
        , TrailingMinusNumbers:=True


And here is my Problem:

how must i write in Cobol the FieldInfo?

Without FieldInfo i can open the TextFile:

           modify xls-app WORKBOOKS::OpenText(
                          by name FileName "c:\\muffrohr\\seq-art.txt",
                          by name Origin               xlMSDOS,
                          by name StartRow             1,
                          by name DataType             xlDelimited,
                          by name TextQualifier        xlNone,
                          by name ConsecutiveDelimiter 0,
                          by name Tab                  0,
                          by name Semicolon            1,
                          by name Comma                0,
                          by name Space                0,
                          by name Other                0,
                          by name TrailingMinusNumbers 1
                                             )
                  giving xls-book.


FieldInfo i have no Idea how to write it...

           modify xls-app WORKBOOKS::OpenText(
                          by name FileName "c:\\muffrohr\\seq-art.txt",
                          by name Origin               xlMSDOS,
                          by name StartRow             1,
                          by name DataType             xlDelimited,
                          by name TextQualifier        xlNone,
                          by name ConsecutiveDelimiter 0,
                          by name Tab                  0,
                          by name Semicolon            1,
                          by name Comma                0,
                          by name Space                0,
                          by name Other                0,
                          by name FieldInfo            array(1, 1),
                                                       array(2, 1),
                                                       array(3, 1),
                                                       array(4, 1),
                                                       array(5, 1),
                                                       array(6, 2),
                                                       array(7, 2),
                                                       array(8, 2),
                                                       array(9, 2),
                                                       array(10, 2),
                                                       array(11, 2),
                                                       array(12, 1),
                                                       array(13, 1),
                                                       array(14, 2),
                                                       array(15, 1),
                                                       array(16, 2),
                                                       array(17, 1),
                                                       array(18, 2),
                                                       array(19, 1),
                                                       array(20, 2),
                                                       array(21, 1),
                                                       array(22, 1),
                          by name TrailingMinusNumbers 1
                                             )
                  giving xls-book.


array is unknow, without array it don't work, tested several variant with

call "C$SETVARIANT" using "...", h-fieldinfo.

But nothing worked :(

Any have an idea how the correct syntax is?

Regards
David Neidinger

PS: sorry for my bad english

thx for the input :)

i had also the problem that i can't modify anything after i open the csv with

modify xls-app WORKBOOKS::OpenText(....) giving xls-book.

for example:
inquire xls-book WORKSHEETS xls-sheets.
cause an error.

I tested to add another sheet - but also causes an error.

If i start a blank excel workbook with
modify xls-app WORKBOOKS::Add() giving xls-book.

all code works, which fails after Workbooks::OpenText :confused:

So i tested to import data like Kelli ( http://www.acucorp.com/support/supported/customer_forum/showthread.php?s=&threadid=481 )

and still workes fine :)

The array to define the columns in imported is a little bit different - so i have to figure out to port your sample code to it.

But i think i can handle it....

Thanks
David

[Migrated content. Thread originally posted on 15 April 2005]

Hello,

i have to import nearly 15.000 data-records into excel from serveral vision-files.

first test:
a direct import into excel
result:
the programm runs 5 hours and then crash with error "not enough memory".

second test:
direct import into excel and save Workbook every 500 data-records (hope that less memory is needed)
result:
same as first test.

third test:
import to a text-file, open it with excel
Problem:
text-lines with "=" at beginning
Solution:
define the Columns as "Text"

So i created a macro in Excel to see how i have to write it:

    Workbooks.OpenText Filename:="C:\\muffrohr\\SEQ-ART.TXT", Origin:=xlMSDOS, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
        Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2), _
        Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array( _
        16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1)) _
        , TrailingMinusNumbers:=True


And here is my Problem:

how must i write in Cobol the FieldInfo?

Without FieldInfo i can open the TextFile:

           modify xls-app WORKBOOKS::OpenText(
                          by name FileName "c:\\muffrohr\\seq-art.txt",
                          by name Origin               xlMSDOS,
                          by name StartRow             1,
                          by name DataType             xlDelimited,
                          by name TextQualifier        xlNone,
                          by name ConsecutiveDelimiter 0,
                          by name Tab                  0,
                          by name Semicolon            1,
                          by name Comma                0,
                          by name Space                0,
                          by name Other                0,
                          by name TrailingMinusNumbers 1
                                             )
                  giving xls-book.


FieldInfo i have no Idea how to write it...

           modify xls-app WORKBOOKS::OpenText(
                          by name FileName "c:\\muffrohr\\seq-art.txt",
                          by name Origin               xlMSDOS,
                          by name StartRow             1,
                          by name DataType             xlDelimited,
                          by name TextQualifier        xlNone,
                          by name ConsecutiveDelimiter 0,
                          by name Tab                  0,
                          by name Semicolon            1,
                          by name Comma                0,
                          by name Space                0,
                          by name Other                0,
                          by name FieldInfo            array(1, 1),
                                                       array(2, 1),
                                                       array(3, 1),
                                                       array(4, 1),
                                                       array(5, 1),
                                                       array(6, 2),
                                                       array(7, 2),
                                                       array(8, 2),
                                                       array(9, 2),
                                                       array(10, 2),
                                                       array(11, 2),
                                                       array(12, 1),
                                                       array(13, 1),
                                                       array(14, 2),
                                                       array(15, 1),
                                                       array(16, 2),
                                                       array(17, 1),
                                                       array(18, 2),
                                                       array(19, 1),
                                                       array(20, 2),
                                                       array(21, 1),
                                                       array(22, 1),
                          by name TrailingMinusNumbers 1
                                             )
                  giving xls-book.


array is unknow, without array it don't work, tested several variant with

call "C$SETVARIANT" using "...", h-fieldinfo.

But nothing worked :(

Any have an idea how the correct syntax is?

Regards
David Neidinger

PS: sorry for my bad english

thx again,

the array works :D


David

[Migrated content. Thread originally posted on 15 April 2005]

Hello,

i have to import nearly 15.000 data-records into excel from serveral vision-files.

first test:
a direct import into excel
result:
the programm runs 5 hours and then crash with error "not enough memory".

second test:
direct import into excel and save Workbook every 500 data-records (hope that less memory is needed)
result:
same as first test.

third test:
import to a text-file, open it with excel
Problem:
text-lines with "=" at beginning
Solution:
define the Columns as "Text"

So i created a macro in Excel to see how i have to write it:

    Workbooks.OpenText Filename:="C:\\muffrohr\\SEQ-ART.TXT", Origin:=xlMSDOS, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
        Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2), _
        Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array( _
        16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1)) _
        , TrailingMinusNumbers:=True


And here is my Problem:

how must i write in Cobol the FieldInfo?

Without FieldInfo i can open the TextFile:

           modify xls-app WORKBOOKS::OpenText(
                          by name FileName "c:\\muffrohr\\seq-art.txt",
                          by name Origin               xlMSDOS,
                          by name StartRow             1,
                          by name DataType             xlDelimited,
                          by name TextQualifier        xlNone,
                          by name ConsecutiveDelimiter 0,
                          by name Tab                  0,
                          by name Semicolon            1,
                          by name Comma                0,
                          by name Space                0,
                          by name Other                0,
                          by name TrailingMinusNumbers 1
                                             )
                  giving xls-book.


FieldInfo i have no Idea how to write it...

           modify xls-app WORKBOOKS::OpenText(
                          by name FileName "c:\\muffrohr\\seq-art.txt",
                          by name Origin               xlMSDOS,
                          by name StartRow             1,
                          by name DataType             xlDelimited,
                          by name TextQualifier        xlNone,
                          by name ConsecutiveDelimiter 0,
                          by name Tab                  0,
                          by name Semicolon            1,
                          by name Comma                0,
                          by name Space                0,
                          by name Other                0,
                          by name FieldInfo            array(1, 1),
                                                       array(2, 1),
                                                       array(3, 1),
                                                       array(4, 1),
                                                       array(5, 1),
                                                       array(6, 2),
                                                       array(7, 2),
                                                       array(8, 2),
                                                       array(9, 2),
                                                       array(10, 2),
                                                       array(11, 2),
                                                       array(12, 1),
                                                       array(13, 1),
                                                       array(14, 2),
                                                       array(15, 1),
                                                       array(16, 2),
                                                       array(17, 1),
                                                       array(18, 2),
                                                       array(19, 1),
                                                       array(20, 2),
                                                       array(21, 1),
                                                       array(22, 1),
                          by name TrailingMinusNumbers 1
                                             )
                  giving xls-book.


array is unknow, without array it don't work, tested several variant with

call "C$SETVARIANT" using "...", h-fieldinfo.

But nothing worked :(

Any have an idea how the correct syntax is?

Regards
David Neidinger

PS: sorry for my bad english

Happy to hear that!