Export data with spaces columns dilimiter

1

Hi All.
I need to export Flat file to SQL Server. The file has spaces delimiter between columns.When tried to export the file to SQL Server I did find space dilimiter in SQL Server wizard export data. I tried to type in delimiter field "Space { }". It didn't help. How specify space delimiter in that process?

Thanks

2

To specify a space delimiter in the SQL Server Import and Export Wizard, you need to use a specific delimiter code instead of typing the word "Space." Here's how you can specify a space delimiter:

  1. Open the SQL Server Import and Export Wizard.
  2. Select the data source and destination.
  3. In the Specify Table Copy or Query step, select the "Write a query to specify the data to transfer" option.
  4. Write a query that selects the data from the flat file using the space delimiter. For example:

SELECT * FROM OPENROWSET(
BULK 'C:\Path\To\Your\File.txt',
FORMATFILE='C:\Path\To\Your\FormatFile.xml'
) AS a

In this query, you'll need to provide the correct file path for your flat file and the format file. The format file is optional but can be used to specify the delimiter and other formatting options.
5. Click Next and follow the remaining steps in the wizard to complete the import process.

By using the OPENROWSET function with a format file or by directly specifying the space delimiter in your query, you can successfully import a flat file with a space delimiter into SQL Server.