Flat file having no delimiterd, values separated by spaces

Transact-SQL
1

I just recently encountered a weird situation where data is in the old report formats being used in application level. It has no delimiters but only spaces. Is there any solution to prepare such data for uploading in SQL server 2014 and above or any sort of VB code to arrange it. Fixed width and right rigged didn't applies. Sample data contains column and data in rows.

Sample data is as under:

IDNO......BRANCH......CUSTOMER_ACCOUNT.....REF_NO......OUTSTANDING AMOUNT......REGION 01.........1004 231342306 97 A005 10002.00 North........ Sales Branch.....Terms....Discount
NRTH.....30 DAYS......10P
Columns names are from IDNO to Discount field and flat file has such multiple records.

2

you may use BCP or BULK INSERT with space as delimiter to upload the file

3

and if at all possible change the delimiter as soon as you can. tab delimiter is my fav. or export the data as xml or json.

1 Like
4

Based on the data given and the words "Fixed Width" in the description, this appears to be a "Fixed Field File" where the spaces aren't actually delimiters. They're just white space to make all of the columns line up, usually left aligned on a given character number with the line of data.

@Techpro ,
If you were to attach a file with at least 5 lines of unadulterated data (include the header and 4 data rows), I'll show you how to do this using 2 different methods.