Solved

How to convert a CSV file into a fixed width file

Posted on 2014-12-10
13
350 Views
Last Modified: 2015-01-01
Hi all,

I'm trying to automate the conversion of a csv file into a fixed width file. I am proposing to use a MSaccess program which would have the file imported into it, carrying out a process and then producing the required file.

to start off with, I was wondering if this is possible?

Thanks
0
Comment
Question by:PipMic
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 300 total points
Comment Utility
Typically this is done by creating an Import specification (by importing the file manually, then saving the import specification )

Have you tried that?


Note that you cant simply "Convert" a file to fixed width, ...the internal structure of the file may need to be modified...

It may also be helpful if you posted a sample of the file before and after conversion
0
 
LVL 29

Expert Comment

by:Olaf Doschke
Comment Utility
Yes, totally possible.

Define an import table with fixed width char fields, import CSV, then export that table into a fixed width text file.

Bye, Olaf.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 300 total points
Comment Utility
I'll be clearer because there are also "saved Imports"

Import/Export
Text File
Browse to the file/location
Select: Import the Source...
Select "Fixed Width"
Adjust the width to align to what you need
...Click through all the other wizards steps, ...stopping at the last screen
Then click "Advanced", and save your import spec with a name.
(to avoid confusion, DO NOT click the box to "save import steps")
...this is NOT the same as the Import specification you just created...

Now you can use this import spec to import other "Similarly formatted" text files with code similar to this:
DoCmd.TransferText acImportFixed, "YourImportSpecName", "TargetTableName", "c:\YourFolder\Yourfile.csv"

JeffCoachman
0
 

Author Comment

by:PipMic
Comment Utility
Thanks guys,

In the first instance I just wanted to know whether it could be done and as usual I perhaps have not explained myself well enough (apologies Coachman!) or probably not used the correct terminology.

An example type of record I am importing is as follows
123456,88888888,A N Other,,900.00

This is a record composed of 5 fields (the fourth in my example is blank but could be populated)

My aim is to import that file (with many similar records) into a program which would then create a file with records with a fixed length, say of 106 characters.

Therefore the data held in the original CSV file would  insert in specific locations of that long record.

So far, Is that still possible? and should I explain further?

Please, I hope I do not offend if I am coming across badly, it is not my intention.

Thanks
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 300 total points
Comment Utility
Yes,

Just run through the wizard and set the width.
...
Then save the Import spec.
Then you can use this same import spec in code for all future  imports of similar files
DoCmd.TransferText acImportFixed, "YourImportSpecName", "TargetTableName", "c:\YourFolder\Yourfile.csv"

Open in new window

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
< (apologies Coachman!) >
No need to apologize, ...I consider us old friends now...
;-)

Jeff
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 100 total points
Comment Utility
You can link to the .csv file.  There is no need to import it unless you need to keep the data.  

Then you need to make a query that will pad the variable fields.  

Numeric values are right justified and zero-filled.  So if you need 9 digits, then 2356 becomes 00002356.  You do that with the Format function -
... Format(SomeField, "000000000").  Use as many zeros as you need to make the field the desired length which in the example is 9 characters.

Text values are left justified and space filled.  The process of space filling is a little more complicated since it requires multiple nested functions.  
... YourField & Space(10 - Nz(Len(YourField), 0))

the number in the space function is the desired length.  the Nz() and Len() are used to figure out how long the field is now so if the length is 5 characters, you'll end up with abcde.....  with the dots being spaces.

Then just export the query.
0
 

Author Comment

by:PipMic
Comment Utility
Coachman ...lolol...thanks
0
 

Author Comment

by:PipMic
Comment Utility
Thanks all,

I am going to work on it..... I'll get back as soon as I can

PatHartman

You have highlighted a concern i have, i.e. padding and space filling:

Could you explain this :-

<<Text values are left justified and space filled.  The process of space filling is a little more complicated since it requires multiple nested functions.  
... YourField & Space(10 - Nz(Len(YourField), 0)) >>

Thanks
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
I explained it but I'll try again.  To "fill" a field with spaces, my example used three functions.  Space() - which returns the specified number of space characters which in the example would be 10.  But, in a file of fields of random width, we can't know ahead of time exactly how many filler characters a particular field in each row will require.  That is where the Nz() and Len() come.  Len() tells us how long a value is so Len("abc") would be 3 but Len() returns null if the field is null so we need Nz() to substitute a 0 when the length of the field is null.  Since 10 is the actual fixed length of the field, we subtract the value returned by the len() function to know how many space characters we need to append to any field.

So if the value is "xyz", Len() will return 3 so 10 - 3 = 7 space characters will be concatenated.
If the value is "abcdefghij", Len() will return 10 so 10 - 10 = 0 so no additional space characters will be concatenated.
If the value is null, Len() will return 0 so 10 -0 = 10  space characters will be concatenated.
0
 

Author Comment

by:PipMic
Comment Utility
Thanks
0
 
LVL 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 100 total points
Comment Utility
>Therefore the data held in the original CSV file would  insert in specific locations of that long record.
Then you really want to know how big you have to dimension the fields/columns of a database table, to be able to import all the CSV data without losing any char, right? Why then ask about a fixed width "file"?

A very simple approach would be creating a table with very large variable length fields, eg all text fields, read the csv data into it and determine max length of trimmed values for each table column. Then you can deduct the needed table for this.

But that would still only be true for that specific csv file. Talk about data export/import in general, if you get csv files on a regular basis, next time some field might have a longer value than you got anytime earlier. Either the structure of an origin changed or the origin field was always larger but only recently has such a long value in it.

In the end a csv file never is complete in that respect and you either have to make assumptions or ask for specifications accompanying the csv file. As a sidenote (rant) I think it's a tendency also very often true for xml given without any xsd structural information, eg max sizes.

So, finally, if you make such a program finding the max widths of all csv values, you still won't have a final truth. In the end you will have to make assumptions or ask for specifications.

Bye, Olaf.
0
 

Author Closing Comment

by:PipMic
Comment Utility
all valid comments
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now