Try this (missing the backslash n on the row terminator)

10.0

5

1SQLCHAR00"\""0Quote1"SQL_Latin1_General_CP1_CI_AS"

2SQLCHAR00"\"|\""1FIRST_NAME"SQL_Latin1_General_CP1_CI_AS"

3SQLCHAR00"\"|\""2LAST_NAME"SQL_Latin1_General_CP1_CI_AS"

4SQLCHAR00"\"|\""3STATE"SQL_Latin1_General_CP1_CI_AS"

5SQLCHAR00"\"\r"4COUNTRY"SQL_Latin1_General_CP1_CI_AS"

If you could illustrate with an example of the format file, it would be easier to see what the problem might be

Try

SELECT * FROM OPENROWSET('MSDASQL',

'Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR=C:\;Extensions=CSV;',

'SELECT * FROM [SampleFile.csv]')

If you are restricted to the Access environment, you could use a SQL Pass-Through Query

Could you supply the CREATE TABLE script for the table you are working on

Being a multi-column PIVOT, this a bit tricky. Here I first shape the data (UNPIVOT) using CROSS APPLY and then PIVOT back on the FR column created in...

Well, at least with the BULK INSERT method I came up with, I first created 2 staging tables. Then for each each file, I needed to BULK INSERT...

Try it. Does it give you the output you are looking for?

I did mock up a BULK INSERT variation but, whilst eliminating the need for a format file, at the same time the specter of RBAR 🙁 was introduced. Or was...

Thanks Jeff, yes it is rather annoying having to create a format file for OPENROWSET BULK, albeit a very simple one in this case. I wonder why that is when...

Using this approach, you will need to construct a format file based on your text file. This method sets up a cte to read your text file with OPENROWSET...

Or curiously

SELECT freedate FROM holidays WHERE freedate = {t '00:00:00'}

Using the first 7 characters will not identify asper as the same vendor as apser. Building a CASE statement in a CTE allows you to pre-map the variant...

Do you get the same discrepancy if you bring the logic in-line using CROSS APPLY?

SELECT TOP 1000 CleanString

FROM customertbl

CROSS APPLY

(

SELECT SUBSTRING(last_name + first_name, N, 1)

...

This approach will need to be refined to suit your requirements but it gives you the gist of a possible solution.

You can identify the files you need using...