IMO it will never change. For example if I've got a product who handles a
first field with 10 chars and another with 4 chars, the vendor could decide
to export its data as a text file using 10 chars for first field and 4 chars
for the second fields...
Whatever the data are it will never change and each line will have always 14
chars... There is no need to ever use more characters as the first field
can't have more than 10 and the other can't have more than 4...
IMO this is the way this file works..
This is know as "fixed size" fields files (as the size of each field never
change) opposed to "delimited" in which you have a delimiter between
fields...
Patrice
--
"jamait" <jamait@discussions.microsoft.com> a écrit dans le message de
news:4BC203B1-40F1-4D24-AC86-CAD43FAC99E2@microsoft.com...[color=blue]
> So by getting the positions for the columns on the first row i can use[/color]
these[color=blue]
> in the following lines?
>
> I think that the column width is set by the max length of the 2nd
> column...so this would probably change if the description in any of the[/color]
rows[color=blue]
> is longer. The file is an export from a different software package that I[/color]
am[color=blue]
> not able to change.
>
> Is there a way to replace 2 or more spaces with a separator or is there a
> better way to split the columns?
>
> Thanks
>
> /Martin
>
> "Patrice" wrote:
>[color=green]
> > Sorry but I'm afraid I still don't catch the exact problem. The two[/color][/color]
lines[color=blue][color=green]
> > you showed us are using the same format.
> >
> > There is NO separator. Each field uses a *fixed* width :
> >
> > Copy the two lines below :
> > A0012430 REKAL TVÄTTMEDEL EKOMAX 0,5L ST 75.9000
> > A0012550 REKAL TVÄTTMEDEL BIOKULÖR 20KGST 1727.0000
> >
> > Paste this into notepad and use the courier new font.
> >
> > You'll see that 5L and 20KG are starting at the same location (and[/color][/color]
always 4[color=blue][color=green]
> > characters wide). ST and ST starts at the same location.
> > The last field doesn't but is right justified in a field that begins at[/color][/color]
some[color=blue][color=green]
> > unknown location (this is the only problem I see, you'll have to find[/color][/color]
out[color=blue][color=green]
> > the *fixed* length of the third field so that you can start reading the[/color][/color]
4 th[color=blue][color=green]
> > field from the correct position).
> >
> > Patrice
> >
> >
> > --
> >
> > "jamait" <jamait@discussions.microsoft.com> a écrit dans le message de
> > news:00D6D172-ABF6-4735-AEFD-6F8D197D1EB4@microsoft.com...[color=darkred]
> > > Hi again,
> > >
> > > It is not a one time operation and what I really want to do is to[/color][/color][/color]
split[color=blue][color=green]
> > each[color=darkred]
> > > line into 4 parts...
> > >
> > > First bit is a ID field, second field a description, third is the unit[/color][/color][/color]
and[color=blue][color=green][color=darkred]
> > > is in the format of an known char array. The last column is the price[/color][/color][/color]
of[color=blue][color=green]
> > the[color=darkred]
> > > product.
> > > Unfortunately when opening this file which is opened as plain text and[/color]
> > read[color=darkred]
> > > line by line the 2nd and 3rd column somehow merges at some of the[/color][/color][/color]
rows...[color=blue][color=green][color=darkred]
> > > The merging only occurs where the description field is long enough and
> > > probably at the position of the longest description in the file...The[/color][/color][/color]
unit[color=blue][color=green]
> > of[color=darkred]
> > > the line is then appended to the line without any delimeter whereas[/color][/color][/color]
the[color=blue][color=green]
> > main[color=darkred]
> > > problem.
> > > The other columns in the file are separated by more than 1 white space
> > > between them.
> > >
> > > I am thinking of using some kind of regular expression and extracting[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > information wanted line by line but not sure how to split the 2nd and[/color][/color][/color]
3rd[color=blue][color=green][color=darkred]
> > > column.
> > > A0012430 REKAL TVÄTTMEDEL EKOMAX 0,5L ST 75.9000
> > > A0012550 REKAL TVÄTTMEDEL BIOKULÖR 20KGST 1727.0000
> > >
> > > Also updated the StreamReader to use the default encoding to display[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > swedish characters properly...
> > >
> > >
> > > Help...
> > >
> > > /Martin
> > >
> > > > >> StreamReader sr = new StreamReader(file,[/color]
> > System.Text.Encoding.Default);[color=darkred]
> > > > >> string line = "";
> > > > >> while ((line = sr.ReadLine()) != null)
> > > > >> {
> > > > >> if(line.Length > 17)
> > > > >> {
> > > > >> DataRow dr = m_Data.NewRow();
> > > > >> dr["Col1"]= line.Substring(0, 17).Trim();
> > > > >> dr["Col2"] = ?
> > > > >> dr["Col3"] = ?
> > > > >> dr["Col4"] = ?
> > > > >> m_Data.Rows.Add(dr);
> > > > >> }
> > > > >> }
> > > > >> sr.Close();
> > >
> > >
> > >
> > >
> > > "Dave" wrote:
> > >
> > > > > The original file include approx. 5000 lines
> > > >
> > > > Is this a one-time operation? If you are using SqlServer you can[/color][/color][/color]
write[color=blue][color=green]
> > a simple DTS package to do the transformation or just use[color=darkred]
> > > > the Import Data command in Enterprise Manager.
> > > >
> > > > --
> > > > Dave Sexton
> > > >
dave@www..jwaonline..com
> > >[/color][/color]
> -----------------------------------------------------------------------[color=green][color=darkred]
> > > > "Patrice" <nobody@nowhere.com> wrote in message[/color]
> > news:unk3M9QYFHA.616@TK2MSFTNGP12.phx.gbl...[color=darkred]
> > > > > Not sure what you meant by "the position seem to be the same[/color][/color][/color]
depending[color=blue][color=green]
> > on[color=darkred]
> > > > > the text in col2" ?
> > > > >
> > > > > What if you try to display this file using a fixed width font such[/color][/color][/color]
as[color=blue][color=green][color=darkred]
> > > > > courier new ? Are all fields aligned ?
> > > > >
> > > > > To me it looks like this is a fixed width file. Each column uses[/color]
> > always the[color=darkred]
> > > > > same range of characters on each line (but it may not be visible[/color]
> > immediately[color=darkred]
> > > > > when using a proportional font).
> > > > >
> > > > > Patrice
> > > > >
> > > > > --
> > > > >
> > > > > "jamait" <jamait@discussions.microsoft.com> a écrit dans le[/color][/color][/color]
message de[color=blue][color=green][color=darkred]
> > > > > news:8EEA78EC-6F35-4C62-BC94-592A262EFA77@microsoft.com...
> > > > >> Hi all,
> > > > >>
> > > > >> I m trying to read in a text file into a datatable...
> > > > >>
> > > > >> Not sure on how to split up the information though, regex or
> > > > > substrings...?
> > > > >>
> > > > >> sample:
> > > > >> Col1 Col2[/color]
> > Col3[color=darkred]
> > > > >> Col4
> > > > >> A0012430 REKAL TVÄTTMEDEL EKOMAX 0,5L ST 75.9000
> > > > >> A0012550 REKAL TVÄTTMEDEL BIOKULÖR 20KGST 1727.0000
> > > > >>
> > > > >> Notice how the 2nd row has merged col2 and col3. There are no[/color]
> > delimeter at[color=darkred]
> > > > >> all but the position seem to be the same depending on the text in[/color]
> > col2.[color=darkred]
> > > > > The
> > > > >> original file
> > > > >> include approx. 5000 lines that I want to update a sql table[/color][/color][/color]
with.[color=blue][color=green]
> > The[color=darkred]
> > > > > above
> > > > >> problem
> > > > >> occurs at many positions in the text file.
> > > > >>
> > > > >> I have successfully read in data from the text file using this[/color][/color][/color]
code:[color=blue][color=green][color=darkred]
> > > > >>
> > > > >> <code>
> > > > >> StreamReader sr = File.OpenText(fileName);
> > > > >> string line = "";
> > > > >> while ((line = sr.ReadLine()) != null)
> > > > >> {
> > > > >> if(line.Length > 17)
> > > > >> {
> > > > >> DataRow dr = m_Data.NewRow();
> > > > >> dr["Col1"]= line.Substring(0, 17).Trim();
> > > > >> dr["Col2"] = ?
> > > > >> dr["Col3"] = ?
> > > > >> dr["Col4"] = ?
> > > > >> m_Data.Rows.Add(dr);
> > > > >> }
> > > > >> }
> > > > >> sr.Close();
> > > > >>
> > > > >> Any help appriciated!
> > > > >>
> > > > >> /Martin
> > > > >>
> > > > >
> > > > >
> > > >
> > > >
> > > >[/color]
> >
> >
> >[/color][/color]
