1

I received raw data from SAP that I need to add to one local database. The issue I have is that I received two separate data sets per table.

  • One header file (describing the Name, Type, Primary Key, Not Null)
  • Actual data file (input to the rows defined in the header file)

I can only add them as flat file as far as I was able to research (and try out), and that means that I can only add one of those files. Either missing the header completely, or missing the input data.

Merging them manually within one CSV file would mean losing all additional information (Type, Primary Key, Not Null, etc.), right?

Any idea how I can proceed?

Thanks for helping me out.

Glad to learn something new here.

Sample header:

+-------------------------------+
|             Col1              |
+-------------------------------+
| TABNAME CHAR 000030 000000    |
| DDLANGUAGE LANG 000001 000000 |
| ...                           |
+-------------------------------+

Sample data: 

+------+-------+------+------+-----+
| Col1 | Col2  | Col3 | Col4 | ... |
+------+-------+------+------+-----+
| LFB1 | ZBOKD | A    | ...  | ... |
| ...  | ...   | ...  | ...  | ... |
+------+-------+------+------+-----+

Merged they would like this (and if I am not mistaken, they need to look like that):

+---------+------------+-----+-----+
| TABNAME | DDLANGUAGE | ... | ... |
+---------+------------+-----+-----+
| LFB1    | ZBOKD      | A   | ... |
| ...     | ...        | ... | ... |
+---------+------------+-----+-----+
Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
T. Deichsel
  • 13
  • 3
  • 1
    Why do you need to import the Header into the database? Tables generally are meant to contain data, not metadata. The table definition is metadata, which should make the Header information redundant. – DeanOC Nov 08 '18 at 20:02
  • @T. Deichsel Some sample data would be helpful. – Mark Kram Nov 08 '18 at 20:07
  • @DeanOC: Thanks for your response. I thought the Metadata is necessary to build up the structure of the table? Essentially to know, what is the primary key? What can be Not null, etc. – T. Deichsel Nov 08 '18 at 20:07
  • @MarkKram: Let me create some sample data real quick. Thanks for the idea. (I can't share the actual data as they are confidential.) – T. Deichsel Nov 08 '18 at 20:09

1 Answers1

0

You will want to CREATE TABLE, and then BULK INSERT into it.

  • Open up your header file and determine what the column names and datatypes are
  • Create your table in SQL Server based off the information in your header file
  • Bulk insert the data file into your table

Even if the header and data were in the same file, you'd ignore the first row since it doesn't contain data.

create table myTable (Col1 <datatype>, Col2 <datatype>, ...)
go

bulk insert myTable
from 'c:\somedirectory\somefile.csv'
   with(
        FIRSTROW = 1
        ,FIELDTERMINATOR = ','
        ,ROWTERMINATOR = '\n'
        ,ERRORFILE = 'c:\someDir\yourErrorFile')

Comma separated files can be a pain, primarily if any value in any column can contain a comma. In this case, SQL Server would treat it like the end of that column. If this is the case, you need to do something outside of SQL Server in PowerShell or Python or whatever to make your file tab delimited, or delimited by another special character that isn't found anywhere in the data.

Also, your ROWTERMINATOR may need to be '0x1E' or another value depending on the source system. Drop the file in NotePad++ or some other text editor that you can see the Unicode symbols on.

S3S
  • 24,809
  • 5
  • 26
  • 45
  • 1
    Thank you very much, @scsimon. I will try that right now! To fully understand it, the Data in the Header doesn't entail info like a) Is it a Primary Key?, b) can it be Not Null? – T. Deichsel Nov 08 '18 at 20:44