Importing Plain Text FIle to MS Acess Database

[DDhillon]

I want to import Plain Text File into MS Access Database. The text file looks
like this:

A & A Drapery Ltd
8314?134A Street?Surrey 604-597-8806
A & A Spice House
107?7045?128th Street?Surrey 604-592-0326
A & A Survey
13430?91st Avenue?Surrey 604-590-1754

Each record is divided into two lines. The first line contains Company Name,
second line contains address and phone. I would like to insert data in 3 dirent
fields company, address and phone.

Is it possible to do?

Thank You

[mxstu]

Yes. If the file is not too big, you could use CFFILE to read the text file
into a variable and then use a loop to read each line of the file (using the
appropriate row delimiter). The odd numbered lines being the company name and
even numbered lines being the address and phone number. You could then split
the address and phone number fields (using the appropriate field delimiter ex.
tab character) and insert each row into the database.

[Stressed_Simon]

This is something along the lines of what you want to do.

This creates an array with is all in, but you can modify it to upload to a DB.

<cffile action="read" file="#CurrentFile#" variable="ThisData">

<cfscript>
// trim text file
ThisData = Trim(ThisData);

// create carriage return line feed
CrLf = Chr(13) & Chr(10);

// create array to hold data
CompanyArray = ArrayNew(1);

// current row
CurrentRow = 1;

// loop through file and get info
for (i = 1; i LTE ListLen(ThisData, CrLf); i = i + 1) {
// is this an address or a company name
if (i MOD 2 IS 1) {
// create new struct
Company = StructNew();

// add to array
CompanyArray[CurrentRow] = Company;

// this is a company name
CompanyArray[CurrentRow].Company.Name = Trim(ListGetAt(ThisData, i, CrLf));

}
// this is an address
else {
// this is the trimmed address and phone number
ThisContact = Trim(ListGetAt(ThisData, i, CrLf));

// this is the phone number
CompanyArray[CurrentRow].Company.Phone = Right(ThisContact, 12);
// this is the address
CompanyArray[CurrentRow].Company.Address = Left(ThisContact,
Len(ThisContact) - 12);

// next row
CurrentRow = CurrentRow + 1;
}
}
</cfscript>

<cfdump var="#CompanyArray#">