Importing txt into Access DB 3

[warlock0199]

Hallo Everybody.

I need some help, I'm very new with Delphi, so I hope you understand what I need.

I have a access database on my application. once a button is pressed a text file(comma separated) should update the database records. I do I do this

 

[martinm]

Two stages here : Get the data into your app., then into Access.

Create a TStringlist, and load the file into this.

MyList := TStringlist.create;
MyList.LoadFromFile('c:\myfile.txt');

As your file is CSV format, you can then put each row into another stringlist to automatically get each field for you.

for i := 0 to (MyList.count-2) do
begin
MyRow := TStringlist.create;
MyRow.commatext := MyList;
for j := 0 to (MyRow.count -1) do
DataAccess.fields[0] := MyRow.items[j] // don't use
//Access myself, but you'll need a StoredProc or
//Query component for DataAccess
DataAcess.Execute; // add the data
end;

 

[warlock0199]

Hi there,

This code could work. The only problem I have is that 'MyRow.commatext’‘breaks the string into comma's and spaces. The file I want to import has commas and spaces.

Is there a different command I can use that will break it up into commas?

 

[donvittorio]

When you are using CommaText, unfortunately it will always treat a space as a delimiter. You have two options to deal with this:

1) you can quote each value in your string, i.e. instead of

value one,value two,value three

use

"value one","value two","value three"

the first string will be broken up into 6 values, the second string will be broken up into just 3 values

2) if you cannot change the format of your text file then you will have to manually parse each line to read the values from it, something like this:

  MyList := TStringlist.create;
  MyList.LoadFromFile('c:\myfile.txt');

  for i := 0 to (MyList.count-1) do
  begin
    CurrPos := 1;
    Line := MyList[i];

    NextPos := PosEx(',', Line, CurrPos);
    //loop through all values in line
    while NextPos <> 0 do
    begin
      Value := Copy(Line, CurrPos, NextPos-1);
      //assign Value to db field here
      CurrPos := NextPos+1;
      NextPos := PosEx(',', Line, CurrPos);
    end;
    //get last value in line
    Value := Copy(Line, CurrPos, Length(Line));
    //assign Value to db field here
  end;

Steve

 

[warlock0199]

Hi there

Thanx for the reply, but I cant get your code to work. Is NextPos, CurrPos, PosEx variables or Delphi functions?

The other thing. Perhaps this way would be easier, Is it possible to read one line of text and then replace each ',' with ","?

Thanx for the help guys!!!!!

 

[donvittorio]

NextPos and CurrPos are variables of type integer. PosEx is a Delphi function which returns the index of a substring in a string, beginning the search at a specified offset (see the help file for further info).
You could replace ',' with "," (see StringReplace function in the help file). Remember to put a double quote at the beginning and end of your line as well.

Steve

 

[svanels]

Posex? which version of delphi? The description is of the Pos function.

How to operate with Strings
faq102-3583


Regards
Steven

 

[donvittorio]

I though PosEx had been around for a while, but a quick bit of research has shown that it was introduced in Delphi 7.

Steve