2

I have a file without any extension and I need to download data into a SQL Server table.

Here's an example of one string opened in Notepad:

7600    20160701    20160701    20160630    20160630    20160630    ZSO ### 5501    850170371

In Excel it looks like one string without any spaces.

How to break string over columns when I have no comma or something like that?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Andriy
  • 123
  • 1
  • 9
  • It's separated with TABs, right? – dean Sep 09 '16 at 08:57
  • show more data... Do you have a CR/LF char at the end of each line to separate records, or is it just a huge line? And @dean's question deserves an answer too . – Thomas G Sep 09 '16 at 08:58
  • when I copy the line from file and pasted it, looks like it has TABs but I am not sure they actualy exsits. I don't have any char at the end of the line. every record just begin with **7600** and ends RUB, nothing else. – Andriy Sep 09 '16 at 09:08
  • SQL Server's Import Data task allows you to specify the column separator (tab perhaps?) or use fixed-width columns. – Panagiotis Kanavos Sep 09 '16 at 10:09

3 Answers3

2

I recommend you to check SQL Server Import wizard which is smart enough to figure out delimiter for you. You may need to validate column width and type, though.

enter image description here

If that is a regular activity you can create a SSIS package (you can actually create SSIS package at the end of import-export wizard) and can implement advance error handling features.

If you are looking for TSQL solution only please post more data/specification so we can work on that. it can be done in any of the following way:

Anuj Tripathi
  • 2,251
  • 14
  • 18
0

For one-off tasks like this with small datasets and variable data quality, my personal preference is to use Excel to create a script which can be run in SSMS.

To accomplish this I would do the following steps:

  1. Split the data using Excels "Text to columns" Text to columns
    • There are similar options here as in SQL Import Wizard as to how columns are calculated (fixed width/delimiters/...) with a pretty intuitive and immediate interface
  2. Copy and paste special Transpose to shift the data into rows Paste special->Transpose
    • Possibly do some manual data cleanup
  3. Add formula to "scriptify" the values Scriptify
  4. Create and store script enter image description here
larsts
  • 451
  • 5
  • 12
-1

You can use Excel (there is a function for this in german it is called (text in spalten, should be something like "text in rows") or LibreOffice Calc (it should provide a wizard) for importing the data. If it looks good in one of these programmes, save it as csv ore another format that you can import to MSSQL.

Mlr
  • 153
  • 1
  • 1
  • 5
  • ETL is an entire subject area. All databases have tools to import and transform data. You *don't* need to use external tools to import simple separated or fixed-length files, there's even a wizard for this. Besides, what you call `csv` can have a lot of variations and locale-specific oddities that require special handling – Panagiotis Kanavos Sep 09 '16 at 10:07
  • Besides, *both* Excel and Calc use a wizard if you *open* a file, but *both* use locale defaults if you double-click on a file. Both are susceptible to localization issues – Panagiotis Kanavos Sep 09 '16 at 10:10