Read from tabbed text file into database

[k1robert]

I'm struggling on this on and would appreciate any help I can get.

I have a huge text file which has about 4000 records. If I open the text file using Programmers File editor I can see that there are page breaks ( chr(12)) between each record.

What I need to do is read the data record by record into

Each Record has 33 lines

BLOGGS                                                                                                           
         Joe                                  MY AREA                                                                  
                                                                901 H60WW        2010                                               
          MyCoM   100410                                                                                                           


                                                LP  60  92  52  A    00  00  0000   M                                               


                                                             My Company                                           
                                                             My Street                                                          
                                                                                                                                    
                                                             London                                                                 
                                       W1P 7RP                                                                                     
                                                                                                                                    
                                                                                                                                    
                                                                                                                                    


                                                                                                                                    
                                                                                                                                    

                                                                                                                                    
                                                 BR                                                                                 


    Dr J Smith                                                                                                                   
    My Area                                                                                                                         
    My Lane                                                                                                                   
    Bromley         BR7 3YZ

so far this is all I have and i am in deperate need for some help

private void readFile()
    {
        string line;
        int counter =0;
        TextReader tr = new StreamReader(@"C:\Shared\P60s.lis");

        while ((line = tr.ReadLine()) != null)
        {
            foreach (var li in line)
            {
               
            }
           
               
        }

        tr.Close();


    }

[finito]

Show some pseudo code

[k1robert]

Ok i'll have a try at this psuedo code

//1 declare variables
string value1, value2, value3; //up to value 33

//2 use textreader to read in log file
read log file line by line

//3 identify the separators in the log file - this is a page break or (chr(12))  
for each line in log file that has page break 
  {
    if line not equal to blank //has no values
    {
      read 1st line store to value1
      read 2nd line store to value2
      read 3rd line store to value2
    }
   // Continue this as there are 33 line to each record
  }

//get the data from the stored values split to get the data
string[] line1 = value1.split(" ");
string surname =line1[0] 
// and i attempt to get all the values  once i get it i insert it into a database
//but inserting into the database is the easy bit

[finito]

Just do .Trim for each line.

[k1robert]

I think i am definitely loosing people on this one so i'll try to explain better. My first post shows an example of just 1 record on a text file that has about 4000 records. Each record is separated by a page break.

Unfortunately for me I cant get the data in a comma delimited format or xml or any other format which would make my life easier.

So I have to write something to manually retrieve the data and store it in a database, the thing is I dont know how to start this which is why i'm asking for help.

I found this link which i'm hoping would set me on the right path and also let you see what i mean.

http://technet.microsoft.com/en-us/library/ms345160(SQL.90).aspx

[k1robert]

this is even closer to what i meant
http://businessintelligencechronicles.blogspot.com/2008/06/non-delimited-text-files-and-script.html

[finito]

Ok answer this for me. what is the Max space between the words or Data?

[thines01]

...if the lines are separated by CR/LF...
You could do something like this:

/*
          * Open Database here
          */
         StreamReader fileIn = new StreamReader(@"c:\science\Test.txt");
         string strData = "";
         List<string> lst_strRecords = new List<string>(33);
         while (!fileIn.EndOfStream)
         {
            strData = fileIn.ReadLine();

            if (string.IsNullOrEmpty(strData))
            {
               lst_strRecords.Add("");
               continue;
            }

            if (strData.Contains((char)12))
            {
               PutRecordsInDatabase(lst_strRecords, dbConnection );
               continue;
            }
            //
            lst_strRecords.Add(strData);
         }
         //
         fileIn.Close();
         /*
          * Close database here 
          */

...which assumes your PutRecordsInDatabase() function will take an array of strings and an open database connection.

The PutRecordsInDatabase() would need to read each line of the string list and put it into the SQL or the parameters for the db update or insert.

Let me know if you need more specific code.