Python Forum
import csv adding a header with pandas
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
import csv adding a header with pandas
#1
Hi,

I'm importing a csv without a header. And I'm trying to tweak my script so that a header is included during load.

In addition to including the header, can I define the field types?

import pandas, csv

from io import StringIO
from sqlalchemy import create_engine

def psql_insert_copy(table, conn, keys, header, data_iter):
   dbapi_conn = conn.connection
   with dbapi_conn.cursor() as cur:
       s_buf = StringIO()
       writer = csv.writer(s_buf)
       writer.writerows(data_iter)
       s_buf.seek(0)
       s_buf.write(header + "\n" + old) #--> test include header
       columns = ', '.join('"{}"'.format(k) for k in keys)
       if table.schema:
           table_name = '{}.{}'.format(table.schema, table.name, columns)
       else:
           table_name = table.name
       sql = 'COPY {} ({}) FROM STDIN WITH CSV'.format(table_name, columns)
       cur.copy_expert(sql=sql, file=s_buf)

engine = create_engine('postgresql://xxxxx:xxxx@xxxx:xxxxx/xxxxxxx')

df = pandas.read_csv("xxxxxx.csv")
df.to_sql('xxx', engine, schema='xxx', method=psql_insert_copy)
the layout of the file I'm importing

Quote:9999999,012021,I,0099999999,000000000099999999+,000000000000000000-,000000000000000000-
9999999,012021,I,0099999999,000000000099999999+,000000000000000000-,000000000000000000-
9999999,012021,I,0099999999,000000000099999999+,000000000000000000-,000000000000000000-
9999999,012021,I,0099999999,000000000099999999+,000000000000000000-,000000000000000000-

Header desejável

cnpj varchar,
data date,
tipo char,
conta varchar,
value integer,
value2 interger,
value3 integer



So, any ideas for implementation?
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  How to import an xml file to Pandas sjhazard 0 2,324 Jun-08-2021, 08:19 PM
Last Post: sjhazard
  Import multiple CSV files into pandas Krayna 0 1,694 May-20-2021, 04:56 PM
Last Post: Krayna
  Pandas Import CSV count between numerical values within 1 Column ptaylor520 3 2,606 Jul-16-2019, 08:13 AM
Last Post: ptaylor520
  import pandas as pd not working in pclinuxos loren41 3 2,270 May-19-2019, 03:49 PM
Last Post: Larz60+
  Trying to import JSON data into Python/Pandas DataFrame then edit then write CSV Rhubear 0 4,075 Jul-23-2018, 09:50 PM
Last Post: Rhubear

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020