A tuff one=create a table to import a txt file with spaces

[dvil]

Here is the txt file layout:
In the Fixed-Feld formats, all records are 80 characters in length. When long CPT code descriptions exceed 72 characters in the fixed-field format, the CPT code is repeated and the sequence number (2-digit counter) is increased by one (eg, 01, 02, 03, etc). Blank space will fill records through position 80 if record descriptions are less than 72 characters in length. Record content includes:

CPT Code - position 1 through 5 (alpha/numeric)
Sequence Number - position 6 though 7 (alpha/numeric)
Blank space - position 8
CPT Long Description - position 9 through 80 (alpha/numeric)

In the Tab-Delimited formats, the codes are NOT sequenced. Each record contains the 5-digit CPT code and its long description in its entirety. The length of each record will vary depending on the description length. Code 88305 is the longest code with a Field 2 description length of 1741 characters. Blank spaces will NOT proceed descriptions shorter than this descriptor length. Record content includes:

CPT Code - Position 1-5 (alpha/numeric)
Tab - position 6
CPT Long Description - position 7 through length of long descriptor (alpha/numeric)

SAMPLE of text file:

0010001 ANESTHESIA FOR PROCEDURES ON SALIVARY GLANDS, INCLUDING BIOPSY
0010201 ANESTHESIA FOR PROCEDURES INVOLVING PLASTIC REPAIR OF CLEFT LIP
0010301 ANESTHESIA FOR RECONSTRUCTIVE PROCEDURES OF EYELID (EG, BLEPHAROPLASTY,
0010302 PTOSIS SURGERY)
0010401 ANESTHESIA FOR ELECTROCONVULSIVE THERAPY
0012001 ANESTHESIA FOR PROCEDURES ON EXTERNAL, MIDDLE, AND INNER EAR INCLUDING
0012002 BIOPSY; NOT OTHERWISE SPECIFIED
0012401 ANESTHESIA FOR PROCEDURES ON EXTERNAL, MIDDLE, AND INNER EAR INCLUDING
0012402 BIOPSY; OTOSCOPY
0012601 ANESTHESIA FOR PROCEDURES ON EXTERNAL, MIDDLE, AND INNER EAR INCLUDING
0012602 BIOPSY; TYMPANOTOMY

Need to know how to create the table to allow for the spaces and the extra long description i.e. 0012401 with 0012402 to import the txt file. then how to make a query to return the full description combining 0012401 with 0012402.

 

[TonyGroves]

You need to use program code to import that data; MySQL can't do it without help.

 

[KarveR]

What Tony said, its pretty straightforward to code in say PHP or bash, but those are relative to OS and what you are prepared to install.



______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[dvil]

interesting karver. Create table with the trim, concat and space atributes cannot accomplish this? The format is described, it is in a txt file, and the rows are consistent.Something like this?

create file cpt,
(code char(5) primaryy key not null,
SeqNum char(2)auto-increment, not null,
tabblank char(1),
long Char(71));

This returns errors but I don't kno how to use the trim,and space atributes.