Use a CSV to create a SQL table

Question

I have a csv file with the following format:

+--------------+--------+--------+--------+
| Description  | brand1 | brand2 | brand3 |
+--------------+--------+--------+--------+
| afkjdjfkafj  |      1 |      0 |      0 |
| fhajdfhjafh  |      1 |      0 |      0 |
| afdkjfkajljf |      0 |      1 |      0 |
+--------------+--------+--------+--------+

I want to write a python script that reads the csv and create a table in sql. I want the table to have the description and the derived brand. If there is a one in the brand name column of the csv then the description is associated with that brand. I want to then create a sql table with the description and the associated brand name.

The table will be :

+-------------+---------------+
| Description | derived brand |
+-------------+---------------+
| afkjdjfkafj | brand 1       |
+-------------+---------------+

So far I have written the code for reading the csv and made the descriptions a list.

df = pd.read_csv(SOURCE_FILE, delimiter=",")
descriptions = df['descriptions'].tolist()

Please provide some guidance on how to read the file and achieve this because I am so lost. Thanks!

Must you use python? SQL Server can use `BULK INSERT` and `OPENROWSET` to import this directly — S3S, Nov 05 '18 at 14:24
Possible duplicate of [Writing a csv file into SQL Server database using python](https://stackoverflow.com/questions/21257899/writing-a-csv-file-into-sql-server-database-using-python) — Salvatore, Nov 05 '18 at 14:27
@scsimon no there is not need to use python but I am not sure how to do so in sql because it is a csv file — Py.rookie89, Nov 05 '18 at 14:30

S3S · Answer 1 · 2018-11-05T15:02:05.877

I just answered a similar question on dba.stackexchange.com, but here's the basics

Create your table...

create table myStagingTable (Description varchar(64), Brand1 bit, Brand2 bit, Brand3 bit)

Then, bulk insert into it but ignore the first row, if your first row has column headers.

bulk insert myStagingTable
   from 'C:\somefile.csv',
   with( firstrow = 2,
         fieldterminator = ',',
         rowterminator = '\n')

Now your data will be in a table just like it is in your excel file.To insert it into your final table, you can use IIF and COALESCE

insert into finalTable
select distinct
    [Description]
    ,DerivedBrand = coalesce(iif(Brand1 = 1,'Brand1',null),iif(Brand2 = 1,'Brand2',null),iif(Brand3 = 1,'Brand3',null))
from myStagingTable

See a DEMO HERE

Thanks! But can I associate the description and the brand? Because it is important for me to only have two columns in the final table. I need to be able to associate the description and find where there is a 1 in the brand columns and then add that as the derived brand to the final table. Is that something I can do in SQL? — Py.rookie89, Nov 05 '18 at 14:47

Use a CSV to create a SQL table

1 Answers1