Read text file with fixed length

[maikels]

I''m trying to read a text file, with cotains fixed text lenght, but i cannot find a way to create a macro for that.
I want to put each string on a cell of a sheet.
I did a research and only found ways to read strings which contains delimiters, but not fixed columns

Exemple of input:

812007092016002700034615070920161330CD113
844012102016002700039701121020160630ID113


How the text should be split into columns in LibreOffice Calc

8120 07092016 00270 00346 15 07092016 1330 C D113
8440 12102016 00270 00397 01 12102016 0630 I D113

columns width: 4, 8, 5, 5, 2, 8, 4, 1, 4

Can anyone help me to build a macro for that?
The text files contains hundreds of line.

Can anyone help me?
Thanks

[Villeroy]

viewtopic.php?f=45&t=83718&p=388849#p388849

Sub ShowFilterOptions returns this:

Code: Select all

FIX,34,76,1,0/1/4/4/12/1/17/1/22/1/24/4/32/1/2147483647/9,0,false,true

is the filter option string to import this file with
UTF8 encoding
default language (not relevant)
Fixed length option
And with fields #2 and #6 marked as DMY dates in order to get proper dates rather than 8digit integers.

[floris v]

There are two ways to do that, one easy, one hard (I think).
Easy: read the file one line after another, then build a new line from that, splitting the line into groups of characters of the right length, and putting them together again with commas as separators, write the new line to a csv file. Finally open the csv file in Calc.
Hard: read the file one line after another, split into groups of characters, insert those into the correct cells in a Calc sheet.

[maikels]

There are two ways to do that, one easy, one hard (I think).
Easy: read the file one line after another, then build a new line from that, splitting the line into groups of characters of the right length, and putting them together again with commas as separators, write the new line to a csv file. Finally open the csv file in Calc.
Hard: read the file one line after another, split into groups of characters, insert those into the correct cells in a Calc sheet.

Could you help me with a exemple of how to split the line into group of characters? So I can put each group in a variable

Or is there a way to read the first x characters of a string, in this case the line read from the file, put them into a into a cell, remove those x characters of the string, and read the next x characters of whats left of the string? Could you help me?

[Villeroy]

Could you help me with a exemple of how to split the line into group of characters?

You know absolutely nothing about programming. Splitting strings into regular chunks is really, really trivial. It is so trivial that even the Base component can do that without macro coding.

I copied the code from the topic I've linked (and which you obviously do not understand neither),
added the path of a test file storing your example data,
added the correct filter options derived from my routine
ran the code and got the correct data (int, date, int, int, int, date, text) imported into a spreadsheet.

Code: Select all

Sub Main
Addr = convertToURL("/tmp/test.csv")
fOptions = "FIX,34,76,1,0/1/4/4/12/1/17/1/22/1/24/4/32/1/2147483647/9,0,false,true"
    Dim Propval(1) as New com.sun.star.beans.PropertyValue
    Propval(0).Name = "FilterName"
    Propval(0).Value = "Text - txt - csv (StarCalc)"
    Propval(1).Name = "FilterOptions"
    Propval(1).Value = fOptions
    oCSVfile = StarDesktop.loadComponentFromURL(Addr, "_blank", 0, Propval())
End Sub

Just another tip: Once you did the manual import in the GUI, you get the same dialog settings as last time when you open the dialog for the next text import. A macro won't save you much time.
Please stop nagging people to write code for you.

[rstetson1]

The gang in redmond had a nifty import screen that let you click along the top and divide the records into various cells - you could then import an entire fixed length record db into the spreadsheet or database, edit various data sets and save as a text file that you could then re-import into your original program.

Was great for making phone #'s the same, or changing settings across many records.

[Villeroy]

The gang in redmond had a nifty import screen that let you click along the top and divide the records into various cells - you could then import an entire fixed length record db into the spreadsheet or database, edit various data sets and save as a text file that you could then re-import into your original program.

Was great for making phone #'s the same, or changing settings across many records.

Well, our import dialog is easy to use as well if not better. However, this genious wanted someone to write him a program so he does not even need any import screen. And then he could not understand the programmatical solution ...

[rstetson1]

Now I'm confused, how do you take the fixed length record and tell the program where the breaks are? I can't get past the programs need for delimiters.

These are COBOL export tiles, there are no delimiters in the files, just a row of characters, text fields have nothing on them as well. The idea is to take the export file, break it into the fields in a spreadsheet or alternative database manager, change the information, save it as a ascii text file and re-import the file back to the original program.

Is there a pure text import to the spreadsheet where you can tell it where the field breaks are?

[Villeroy]

There is a "Fixed width" option in the import dialog. Choose it and mark the column widths in the table preview.
There is also a [Help] button on that dialog.

[FJCC]

Open a new spreadsheet and:
Select the menu Insert -> Sheet From File.
Select the File
In the import dialog click Fixed Width
When the first few lines are displayed at the bottom of the dialog, click on the ruler to set the column breaks.

[Villeroy]

And we have a database component. Its underlying database engine supports text tables linked to plain text files and a rather simple query can split up the incoming data into multiple fields of defined data types.

[rstetson1]

Thanks - did look at Insert -> sheet from file

I wondered why that kind of import was in Office and not in ours.

Thanks

rsl

[Villeroy]

Insert>Sheet From File... shows the same text import dialog with "Fixed width" option when you point it to a text file.
We do have a tutorial section here.
[Tutorial] External Links In Calc

[rstetson1]

Thank you for your help - I'm using the software at a client site - replacing an old Unix WordPerfect setup for processing.

We have AcuXDBC to get into the accounting files and are using Pivot Tables to develop custom reporting. That works fine.

But I am having issues using the report writer. Will check out the tutorial section, My big issue is developing report templates where they can key8 in a date range, and the system pulls the data out and reports it. I'm used to some of the older canned writers that were set to work exclusively with the software.

But that's my problem, and I don't want anyone writing code for me either.... Thanks again.

rsl

[Villeroy]

You know parameter queries in Base? SELECT * FROM "XTABLE" WHERE "DATE_FIELD" BETWEEN :From_Date AND :Until_Date
You can link pivot tables, import ranges, serial letters and reports to it. On every refresh a dialog pops up where you key in the parameter substitutions.
When you link subforms to parameter queries, you can link the named parameters to fields in the parent form. Then the subforms parameters are substituted by linked field values from the parent form's current record.
Form substitution with optional parameters:
SELECT * FROM "XTABLE" WHERE ("DATE_FIELD" >= :From_Date OR :From_Date IS NULL) AND ("DATE_FIELD" <= :Until_Date OR :Until_Date IS NULL)
Example file (see form "Filter Data"): download/file.php?id=26483

[rstetson1]

I'm not that far in the programming manual..... <g>

I have a very good Pivot table that gives open POs, but I'm still working on having them put in dates and having the software kick out a report. The help screens aren't all that helpful when you're trying to generate finished products and you're starting in the middle of the process.

Thanks again.

rsl

[Villeroy]

You will not find SQL in the programming manuals. Query definition are written in plain, standard conformant SQL, stored in database documents similar to spreadsheet formulas stored in spreadsheets.

[rstetson1]

Thanks - I have a bit to learn yet. At least I know where the tutorials are now.