[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

read excel (csv) file

Posted on 2003-12-11
7
Medium Priority
?
1,163 Views
Last Modified: 2010-04-03
hi all ...

How can I read a CSV file and read the data in each column ?

thanks alot ;)
0
Comment
Question by:allie910
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 5

Accepted Solution

by:
snehanshu earned 90 total points
ID: 9926012
allie910,
  CSV is comma separated values and an easy way to tackle it is the CommaText property of a TStringList.

Here's a sample code that loads the CSV file into a string list, and uses another stringlist to get columns of each row.

Procedure MyCSVProcess(FileName: String);
Var
  MyFile, MyColumns: TStringList;
  ROwCtr, ColCtr: integer;
begin
  MyFile := TStringList.Create;
  MyColumns := TStringList.Create;
  If fileexists(FileName) then
  try
  begin
    MyFile.LoadFromFile(FileName);//read CSV file
    For RowCtr := 0 to MyFile.Count-1 Do
    Begin
      //Load RowCtrth row of CSV file into MyColumns
      //MyColumns[0] to MyColumns[MyColumns.Count-1] are the column values
      MyColumns.CommaText := MyFile[RowCtr];
      For ColCtr := 0 to MyColumns.Count -1 Do
      Begin
        //process columns here
        MyColumns[ColCtr] := MyColumns[ColCtr];
      End;
      //To edit the CSV file
      MyFile[RowCtr] := MyColumns.CommaText;
    End;

  end
  finally
  begin
    MyFile.SaveToFile(FileName);//To save changes
    MyFile.Free;
    MyColumns.Free;
  end;
  end;

end;


HTH,
...Snehanshu
0
 
LVL 5

Expert Comment

by:snehanshu
ID: 9926144
allie910,
  Another way to access a CSV file is by using tables but I am not sure if that allows editing.
  I just discovered this information at:
http://www.experts-exchange.com/Programming/Programming_Languages/Delphi/Q_10137944.html

You can import text files to your dbase table if they are delimited, such as a comma or space, etc.  You need two things. One, set up a ASCII (text) table.  Use the TTable component and set the table type to ttASCII. Two, a dbase table set up to receive your records.  

For more information, I've attached the file Asciidrv.txt that comes with Delphi 1 :

Using ASCII Tables with Delphi
-------------------------------
Delphi supports using ASCII text files as tables, with limited
functionality.  ASCII tables are intended for exporting
and importing data to and from other formats--in general, they are
not recommended for use as data sources for applications.

Each ASCII table requires a data file (generally with .TXT
extension) and a schema file (with the same base file name
and .SCH extension). The schema file contains information about
the structure of the table and the datatypes of its columns (fields).  

ASCII tables are always opened for exclusive access.  In other
words, no more than one application (session) can access an
ASCII table at one time.  If you have opened an ASCII table at
design time, you must close it before running your application
that opens the table at run time.

Copying a table to ASCII format with Database Desktop or a TBatchmove
component will automatically create a schema file as well as the
ASCII data file.  ASCII tables are always created with FIXED filetype.
When creating an ASCII table with Delphi (for example with BatchMove
or CreateTable methods), you must specify TableType as ttASCII.  You
should not leave TableType as ttDefault.

ASCII tables do not support the following:
* Indexes (and therefore, any methods or functions that require an
  index, such as GoToKey, SetRange, etc.)
* TQuery components (SQL)
* Deleting records
* Inserting records.  Insert always appends the record to the end
  of the table.
* Referential Integrity
* Blob datatypes
* DELIMITED tables do not allow modification (editing) of records

The Schema File
---------------
All information in the schema file is case-insensitive.
Here is a sample of what a schema file looks like:

[CUSTOMER]                       // File name with no extension.
FILETYPE = VARYING           // Format: VARYING or FIXED
CHARSET = ascii               // Language driver name.
DELIMITER = "                  // Delimiter for char fields.
SEPARATOR = ,                    // Separator character
Field1 = Name,CHAR,12,0,0     // Field information
Field2 = Salary,FLOAT,8,2,12      

The schema file has a format similar to Windows INI files.
The file begins with the name of the table in brackets.
The second line specifies the file format following the
keyword FILETYPE: FIXED or VARYING.
* In a FIXED format file, each field always takes up a fixed
  number of characters in the file, and the data is padded with
  blanks as needed.
* In a VARYING file, each field takes a variable number of characters,
  each character field is enclosed by DELIMITER characters, and the
  fields are separated by a SEPARATOR character.  The DELIMITER and
  SEPARATOR must be specified for a VARYING format file, but
  not for a FIXED format file.

The CHARSET attribute specifies the name of the language
driver to use.  This is the base file name of the .LD
file used for localization purposes.

The remaining lines specify the attributes of the table's
fields (columns). Each line must begin with "Fieldx = ",
where x is the field number (i.e. Field1, Field2, and so on).
Then comes a comma-delimited list specifying:
* Field name - Same restrictions as Paradox field names.
* Datatype - The field data type.  See below.
* Number of characters or units.  Must be <= 20 for numeric
  data types.  Total maximum number of characters for date/time
  datatypes (including / and : separators).
* Number of digits after the decimal (FLOAT only).
* Offset - Number of characters from the beginning of the line
  that the field begins.  Used for FIXED format only.

The following data types are supported:
CHAR - Character
FLOAT - 64-bit floating point
NUMBER - 16-bit integer
BOOL - Boolean (T or F)
LONGINT - 32-bit long integer
DATE - Date field.  Format specified by IDAPI.CFG
TIME - Time field. Format specified by IDAPI.CFG
TIMESTAMP - Date + Time field. Format specified by IDAPI.CFG

NOTE: You can specify Date and time formats in the BDE configuration
utility  

Example 1 - VARYING format file
-------------------------------
CUSTOMER.SCH:

[CUSTOMER]
Filetype=VARYING
Delimiter="
Separator=,
CharSet=ascii
Field1=Customer No,Float,20,04,00
Field2=Name,Char,30,00,20
Field3=Phone,Char,15,00,145
Field4=First Contact,Date,11,00,160

CUSTOMER.TXT:

1221.0000,"Kauai Dive Shoppe","808-555-0269",04/03/1994
1231.0000,"Unisco","809-555-3915",02/28/1994
1351.0000,"Sight Diver","357-6-876708",04/12/1994
1354.0000,"Cayman Divers World Unlimited","809-555-8576",04/17/1994
1356.0000,"Tom Sawyer Diving Centre","809-555-7281",04/20/1994

Example 2 - FIXED format file
-----------------------------
CUSTOMER.SCH:

[CUSTOMER]
Filetype=Fixed
CharSet=ascii
Field1=Customer No,Float,20,04,00
Field2=Name,Char,30,00,20
Field3=Phone,Char,15,00,145
Field4=First Contact,Date,08,00,160

CUSTOMER.TXT:

           1221.0000Kauai Dive Shoppe             808-555-0269   04/03/94
           1231.0000Unisco                        809-555-3915   02/28/94
           1351.0000Sight Diver                   357-6-876708   04/12/94
           1354.0000Cayman Divers World Unlimited 809-555-8576   04/17/94
           1356.0000Tom Sawyer Diving Centre      809-555-7281   04/20/94

Here's another thread where we discussed CSV files:

http://www.experts-exchange.com/Programming/Programming_Languages/Delphi/Q_20744932.html

HTH,
...Snehanshu
0
 
LVL 8

Expert Comment

by:gmayo
ID: 9926806
Only problem with CommaText is that it, by default, treats spaces as delimiters too. If you can tell Excel to enclose cells with spaces in with quotes then you'll be okay.

For example:
A,B C,D
will come out like
A
B
C
D
instead of
A
B C
D
What you need is
A,"B C",D

You might want to read the line manually and go through it character by character, seperating the columns with commas.

Geoff M.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:smurff
ID: 9926848
Hi,

The easyest was for me when I had to do this was to use TAdvStringGrid from www.tmssoftware.com
The grid can be created dynamically and with commands like AdvStringGrid1.LoadFromCSV makes it all easy.
Then you just read the data like any other grid. ShowMessage(AdvStringGrid1.Cells[2,2]);
It has loads of functions including SaveToXls etc etc

Regards
Smurff
0
 
LVL 4

Expert Comment

by:JeePeeTee
ID: 9945502
Take a look at: http://www.scalabium.com/smi/index.htm

Great tool for importing all kinds of formats ....

Regards,

JPT
0
 

Author Comment

by:allie910
ID: 9954284
Hi all,

Thanks for all the help... ^^
0
 
LVL 5

Expert Comment

by:snehanshu
ID: 9954359
Glad to help.
...Shu
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question