Import Fixed Width with Header rows LeAnn 06.07.2006 22:41:02 Hi, I've read several posts regarding this topic and they have been very helpful but I still haven't resolved my problem. I have a fixed width .dat file. The manufacturer says that each line has 24 characters and the last 2 characters are <CRLF>. When I use the import wizard and create a spec for the import it imported fine EXCEPT it imported an empty row between each record. After reading John Nuriks postings I investigated the file using HexEdit and saw that there seemed to be an extra OD character. Each line ends with ODOD OA. Another issue may be that there are 3 header rows of which have different lengths (some more than 24 characters) - but they all begin with H and that there is more than one "set of data" - meaning 3 header rows appear multiple times in the file. I was planning on writing a VB procedure to loop through and skip the header rows but I don't know how to tell it to recognize the ODOD OA characters. I hope I have explain this clearly enough for someone to help me. Thanks LeAnn

Re: Import Fixed Width with Header rows John Nurick <j.mapSoN.nurick[ at ]dial.pipex.com> 07.07.2006 05:14:29 Hi LeAnn, This is air code but shows how to work through a file. I think all you'll need to get rid of the superfluous carriage returns is the If Right(strLine, 1)... structure. Dim lngFIn as Long Dim lngFOut as Long Dim strLine as String lngFIn = FreeFile() Open "C:\folder\source.txt" For Input As #lngFIn lngFOut = FreeFile() Open "C:\folder\destination.txt" For Output As #lngFOut Do Until Eof(lngFIn) 'Read line Line Input #lngFIn, strLine If Left(strLine, 1) <> "H" Then 'not a header If Right(strLine, 1) = vbCR '&H0D strLine = Left(strLine, Len(strLine) - 1) End If Print #lngFOut, strLine End If Loop Close #lngFIn Close #lngFOut If you find the output file has blank lines, it means that Line Input is treating the extra 0Ds as line breaks. In that case, wrap the Print statement in If Len(strLine) > 0 Then Print... End If Is it really safe to just dump the header lines, or do they contain grouping information that you need to incorporate into your table? On Thu, 6 Jul 2006 15:41:02 -0700, LeAnn <LeAnn[ at ]discussions.microsoft.com> wrote: [Quoted Text] >Hi, > >I've read several posts regarding this topic and they have been very helpful >but I still haven't resolved my problem. I have a fixed width .dat file. >The manufacturer says that each line has 24 characters and the last 2 >characters are <CRLF>. > >When I use the import wizard and create a spec for the import it imported >fine EXCEPT it imported an empty row between each record. After reading John >Nuriks postings I investigated the file using HexEdit and saw that there >seemed to be an extra OD character. Each line ends with ODOD OA. Another >issue may be that there are 3 header rows of which have different lengths >(some more than 24 characters) - but they all begin with H and that there is >more than one "set of data" - meaning 3 header rows appear multiple times in >the file. > >I was planning on writing a VB procedure to loop through and skip the header >rows but I don't know how to tell it to recognize the ODOD OA characters. > >I hope I have explain this clearly enough for someone to help me. >Thanks >LeAnn > -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email.

Re: Import Fixed Width with Header rows LeAnn 07.07.2006 17:35:02 Absolutely marvelous!! Works like a charm and yes the Line Input did add the extra line. For our purposes it is safe to dump the header rows (thank goodness!) Thanks for your help! "John Nurick" wrote: [Quoted Text] > Hi LeAnn, > > This is air code but shows how to work through a file. I think all > you'll need to get rid of the superfluous carriage returns is the > If Right(strLine, 1)... > structure. > > Dim lngFIn as Long > Dim lngFOut as Long > Dim strLine as String > > lngFIn = FreeFile() > Open "C:\folder\source.txt" For Input As #lngFIn > lngFOut = FreeFile() > Open "C:\folder\destination.txt" For Output As #lngFOut > > Do Until Eof(lngFIn) > 'Read line > Line Input #lngFIn, strLine > > If Left(strLine, 1) <> "H" Then 'not a header > > If Right(strLine, 1) = vbCR '&H0D > strLine = Left(strLine, Len(strLine) - 1) > End If > > Print #lngFOut, strLine > End If > Loop > > Close #lngFIn > Close #lngFOut > > If you find the output file has blank lines, it means that > Line Input > is treating the extra 0Ds as line breaks. In that case, wrap the Print > statement in > If Len(strLine) > 0 Then > Print... > End If > > Is it really safe to just dump the header lines, or do they contain > grouping information that you need to incorporate into your table? > > > > On Thu, 6 Jul 2006 15:41:02 -0700, LeAnn > <LeAnn[ at ]discussions.microsoft.com> wrote: > > >Hi, > > > >I've read several posts regarding this topic and they have been very helpful > >but I still haven't resolved my problem. I have a fixed width .dat file. > >The manufacturer says that each line has 24 characters and the last 2 > >characters are <CRLF>. > > > >When I use the import wizard and create a spec for the import it imported > >fine EXCEPT it imported an empty row between each record. After reading John > >Nuriks postings I investigated the file using HexEdit and saw that there > >seemed to be an extra OD character. Each line ends with ODOD OA. Another > >issue may be that there are 3 header rows of which have different lengths > >(some more than 24 characters) - but they all begin with H and that there is > >more than one "set of data" - meaning 3 header rows appear multiple times in > >the file. > > > >I was planning on writing a VB procedure to loop through and skip the header > >rows but I don't know how to tell it to recognize the ODOD OA characters. > > > >I hope I have explain this clearly enough for someone to help me. > >Thanks > >LeAnn > > > > -- > John Nurick [Microsoft Access MVP] > > Please respond in the newgroup and not by email. >

Re: Import Fixed Width with Header rows LeAnn 10.07.2006 21:57:02 FYI I was going to click the "Helpful" button on your post but it isn't there anymore. "John Nurick" wrote: [Quoted Text] > Hi LeAnn, > > This is air code but shows how to work through a file. I think all > you'll need to get rid of the superfluous carriage returns is the > If Right(strLine, 1)... > structure. > > Dim lngFIn as Long > Dim lngFOut as Long > Dim strLine as String > > lngFIn = FreeFile() > Open "C:\folder\source.txt" For Input As #lngFIn > lngFOut = FreeFile() > Open "C:\folder\destination.txt" For Output As #lngFOut > > Do Until Eof(lngFIn) > 'Read line > Line Input #lngFIn, strLine > > If Left(strLine, 1) <> "H" Then 'not a header > > If Right(strLine, 1) = vbCR '&H0D > strLine = Left(strLine, Len(strLine) - 1) > End If > > Print #lngFOut, strLine > End If > Loop > > Close #lngFIn > Close #lngFOut > > If you find the output file has blank lines, it means that > Line Input > is treating the extra 0Ds as line breaks. In that case, wrap the Print > statement in > If Len(strLine) > 0 Then > Print... > End If > > Is it really safe to just dump the header lines, or do they contain > grouping information that you need to incorporate into your table? > > > > On Thu, 6 Jul 2006 15:41:02 -0700, LeAnn > <LeAnn[ at ]discussions.microsoft.com> wrote: > > >Hi, > > > >I've read several posts regarding this topic and they have been very helpful > >but I still haven't resolved my problem. I have a fixed width .dat file. > >The manufacturer says that each line has 24 characters and the last 2 > >characters are <CRLF>. > > > >When I use the import wizard and create a spec for the import it imported > >fine EXCEPT it imported an empty row between each record. After reading John > >Nuriks postings I investigated the file using HexEdit and saw that there > >seemed to be an extra OD character. Each line ends with ODOD OA. Another > >issue may be that there are 3 header rows of which have different lengths > >(some more than 24 characters) - but they all begin with H and that there is > >more than one "set of data" - meaning 3 header rows appear multiple times in > >the file. > > > >I was planning on writing a VB procedure to loop through and skip the header > >rows but I don't know how to tell it to recognize the ODOD OA characters. > > > >I hope I have explain this clearly enough for someone to help me. > >Thanks > >LeAnn > > > > -- > John Nurick [Microsoft Access MVP] > > Please respond in the newgroup and not by email. >

Re: Import Fixed Width with Header rows "Jeff Glatt" <nospam[ at ]somewhere.com> 07.09.2006 18:24:50 Try this control. http://www.springsys.com/netproducts/orchid.asp [Quoted Text] > Hi, > > I've read several posts regarding this topic and they have been very > helpful > but I still haven't resolved my problem. I have a fixed width .dat file. > The manufacturer says that each line has 24 characters and the last 2 > characters are <CRLF>. > > When I use the import wizard and create a spec for the import it imported > fine EXCEPT it imported an empty row between each record. After reading > John > Nuriks postings I investigated the file using HexEdit and saw that there > seemed to be an extra OD character. Each line ends with ODOD OA. Another > issue may be that there are 3 header rows of which have different lengths > (some more than 24 characters) - but they all begin with H and that there > is > more than one "set of data" - meaning 3 header rows appear multiple times > in > the file. > > I was planning on writing a VB procedure to loop through and skip the > header > rows but I don't know how to tell it to recognize the ODOD OA characters. > > I hope I have explain this clearly enough for someone to help me. > Thanks > LeAnn > >

Re: Import Fixed Width with Header rows John Nurick <j.mapSoN.nurick[ at ]dial.pipex.com> 07.09.2006 21:00:40 Hi LeAnn, I've experimented briefly and it seems that you don't need to do anything special. The old Line Input statement appears to treat either CR (0d) or CRLF (0d0a) as line terminators; thus a file whose records are separated by 0d0d0a is read as if it had a blank line after every line of data. Here's a snippet that reads the file and dumps the bogus blank lines. Dim FH As Long Dim S As String FH = FreeFile() Open "C:\temp\crcrlf.txt" For Input As #FH Do Until EOF(FH) 'Read a line. This seems read from the current 'position in the file to the first CR or CRLF Line Input #FH, S 'S now contains either a line from the file 'excluding its terminating 0d0d0a, (i.e. VBA has 'read from the start of the line up to and including 'the first CR, which it has dumped), or else an 'empty string (i.e. VBA has read from after the 'first CR up to and including the CRLF immediately 'after, which it has dumped). If Len(S) > 0 Then Debug.Print S 'or do whatever you like End If Loop Close #FH So that's one problem sorted. As for the headers: can you just dump them, or do you need to collect values from the headers and include them in the individual data rows? [Quoted Text] >> I've read several posts regarding this topic and they have been very >> helpful >> but I still haven't resolved my problem. I have a fixed width .dat file. >> The manufacturer says that each line has 24 characters and the last 2 >> characters are <CRLF>. >> >> When I use the import wizard and create a spec for the import it imported >> fine EXCEPT it imported an empty row between each record. After reading >> John >> Nuriks postings I investigated the file using HexEdit and saw that there >> seemed to be an extra OD character. Each line ends with ODOD OA. Another >> issue may be that there are 3 header rows of which have different lengths >> (some more than 24 characters) - but they all begin with H and that there >> is >> more than one "set of data" - meaning 3 header rows appear multiple times >> in >> the file. >> >> I was planning on writing a VB procedure to loop through and skip the >> header >> rows but I don't know how to tell it to recognize the ODOD OA characters. >> >> I hope I have explain this clearly enough for someone to help me. >> Thanks >> LeAnn >> >> > -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email.