read_csv

In [ ]:
read_csv(path: str,
         cursor = None, 
         schema: str = "",
         table_name: str = '', 
         sep: str = ',', 
         header: bool = True,
         header_names: list = [],
         na_rep: str = '', 
         quotechar: str = '"', 
         escape: str = '\\', 
         genSQL: bool = False,
         parse_n_lines: int = -1,
         insert: bool = False,
         temporary_table: bool = False,
         temporary_local_table: bool = True,)

Ingests a CSV file using flex tables.

Parameters

Name Type Optional Description
path
str
Absolute path where the CSV file is located.
cursor
DBcursor
Vertica DB cursor.
schema
str
Schema where the CSV file will be ingested.
table_name
str
Final relation name.
sep
str
Column separator.
header
bool
If set to False, the parameter 'header_names' will be to use to name the different columns.
header_names
list
List of the columns names.
na_rep
str
Missing values representation.
quotechar
str
Char which is enclosing the str values.
escape
str
Separator between each record.
genSQL
bool
If set to True, the SQL code to use to create the final table will be generated but not executed. It is a good way to change the final relation types or to customize the data ingestion.
parse_n_lines
int
If this parameter is greater than 0. A new file of 'parse_n_lines' lines will be created and ingested first to identify the data types. It will be then dropped and the entire file will be ingested. The data types identification will be less precise but this parameter can make the process faster if the file is heavy.
insert
bool
If set to True, the data will be ingested to the input relation. Be sure that your file has a header corresponding to the name of the relation columns otherwise the ingestion will not work.
temporary_table
bool
If set to True, a temporary table will be created.
temporary_local_table
bool
If set to True, a temporary local table will be created. The parameter 'schema' must to be empty, otherwise this parameter is ignored.

Returns

vDataFrame : The vDataFrame of the relation.

Example

In [1]:
from verticapy.utilities import *
# Gen the SQL needed to create the Table
read_csv("titanic.csv", 
         table_name = "titanic_dataset",
         schema = "public",
         quotechar = '"',
         sep = ",",
         na_rep = "",
         genSQL = True)
CREATE TABLE "public"."titanic_dataset"("pclass" Integer, "survived" Integer, "name" Varchar(164), "sex" Varchar(20), "age" Numeric(6,3), "sibsp" Integer, "parch" Integer, "ticket" Varchar(36), "fare" Numeric(10,5), "cabin" Varchar(30), "embarked" Varchar(20), "boat" Varchar(100), "body" Integer, "home.dest" Varchar(100));
COPY "public"."titanic_dataset"("pclass", "survived", "name", "sex", "age", "sibsp", "parch", "ticket", "fare", "cabin", "embarked", "boat", "body", "home.dest") FROM {} DELIMITER ',' NULL '' ENCLOSED BY '"' ESCAPE AS '\' SKIP 1;
In [2]:
# Gen the SQL needed to create the Table & Parses only 100 lines 
read_csv("titanic.csv", 
         table_name = "titanic_dataset",
         schema = "public",
         quotechar = '"',
         sep = ",",
         na_rep = "",
         parse_n_lines = 100,
         genSQL = True)
CREATE TABLE "public"."titanic_dataset"("pclass" Integer, "survived" Integer, "name" Varchar(130), "sex" Varchar(20), "age" Numeric(5,3), "sibsp" Integer, "parch" Integer, "ticket" Varchar(22), "fare" Numeric(10,5), "cabin" Varchar(22), "embarked" Varchar(20), "boat" Varchar(100), "body" Integer, "home.dest" Varchar(92));
COPY "public"."titanic_dataset"("pclass", "survived", "name", "sex", "age", "sibsp", "parch", "ticket", "fare", "cabin", "embarked", "boat", "body", "home.dest") FROM {} DELIMITER ',' NULL '' ENCLOSED BY '"' ESCAPE AS '\' SKIP 1;
In [6]:
# Ingests the CSV file
read_csv("titanic.csv", 
         table_name = "titanic_dataset",
         schema = "public",
         quotechar = '"',
         sep = ",",
         na_rep = "",
         parse_n_lines = 100)
The table "public"."titanic_dataset" has been successfully created.
123
fare
Numeric(10,5)
123
survived
Int
Abc
sex
Varchar(20)
Abc
cabin
Varchar(22)
Abc
boat
Varchar(100)
123
pclass
Int
Abc
home.dest
Varchar(92)
Abc
ticket
Varchar(22)
Abc
embarked
Varchar(20)
123
body
Int
123
age
Numeric(5,3)
123
parch
Int
Abc
Varchar(130)
123
sibsp
Int
1151.550000femaleC22 C26[null]1Montreal, PQ / Chesterville, ON113781S[null]2.00021
2151.550000maleC22 C26[null]1Montreal, PQ / Chesterville, ON113781S13530.00021
3151.550000femaleC22 C26[null]1Montreal, PQ / Chesterville, ON113781S[null]25.00021
40.000000maleA36[null]1Belfast, NI112050S[null]39.00000
549.504200male[null][null]1Montevideo, UruguayPC 17609C2271.00000
6227.525000maleC62 C64[null]1New York, NYPC 17757C12447.00001
725.925000male[null][null]1New York, NYPC 17318S[null][null]00
8247.520800maleB58 B60[null]1Montreal, PQPC 17558C[null]24.00010
975.241700maleC6A1Winnipeg, MN13050C[null]36.00000
1026.000000male[null][null]1San Francisco, CA13905C14825.00000
1135.500000maleT[null]1Trenton, NJ113784S[null]45.00000
1226.550000maleD22[null]1London / Winnipeg, MB110489S[null]42.00000
1330.500000maleA21[null]1Pomeroy, WA113054S[null]41.00000
1450.495800maleB10[null]1Omaha, NEPC 17591C20848.00000
1539.600000male[null][null]1Philadelphia, PA112379C[null][null]00
1626.550000maleB38[null]1Washington, DC113050S[null]45.00000
1731.000000male[null][null]1[null]113798S[null][null]00
185.000000maleB51 B53 B55[null]1New York, NY695S[null]33.00000
1947.100000male[null][null]1Montevideo, Uruguay113059S[null]28.00000
2047.100000male[null][null]1Montevideo, Uruguay113059S[null]17.00000
2126.000000male[null][null]1Ascot, Berkshire / Rochester, NY19924S[null]49.00000
2278.850000maleC46[null]1Little Onn Hall, Staffs19877S17236.00001
2361.175000maleE31[null]1Amenia, NDW.E.P. 5734S[null]46.00001
240.000000male[null][null]1Liverpool, England / Belfast112051S[null][null]00
25136.779200maleC89[null]1Los Angeles, CA13508C[null]27.00001
2652.000000maleA14[null]1Stoughton, MA110465S[null][null]00
2725.587500maleE58[null]1Victoria, BC5727S[null]47.00000
2883.158300maleE52[null]1Lakewood, NJPC 17756C[null]37.00011
2926.550000male[null][null]1Roachdale, IN113791S[null][null]00
3071.000000maleB22[null]1Milwaukee, WIWE/P 5735S26970.00011
3171.283300maleC85[null]1New York, NYPC 17599C[null]39.00001
3252.000000maleB71[null]1Montreal, PQF.C. 12750S[null]31.00001
33106.425000maleC86[null]1Deephaven, MN / Cedar Rapids, IAPC 17761C6250.00001
3429.700000maleA18[null]1Philadelphia, PAPC 17580C13339.00000
3531.679200femaleA29[null]1New York, NYPC 17531C[null]36.00000
36221.779200maleC95[null]1[null]PC 17483S[null][null]00
3727.750000maleC111[null]1New York, NY113051C[null]30.00000
38263.000000maleC23 C25 C27[null]1Winnipeg, MB19950S[null]19.00023
39263.000000maleC23 C25 C27[null]1Winnipeg, MB19950S[null]64.00041
4026.550000maleD34[null]1Westcliff-on-Sea, Essex113778S[null][null]00
410.000000maleB102[null]1[null]112058S[null][null]00
4253.100000maleC123[null]1Scituate, MA113803S[null]37.00001
4338.500000maleE63[null]1St Anne's-on-Sea, Lancashire111320S27547.00000
4479.200000maleB86[null]1[null]PC 17593C[null]24.00000
4534.654200maleA5[null]1New York, NYPC 17754C[null]71.00000
46153.462500maleC91[null]1Winnipeg, MBPC 17582S14738.00010
4779.200000maleB82 B84[null]1New York, NYPC 17593C[null]46.00000
4842.400000male[null][null]1[null]113796S[null][null]00
4983.475000maleC83[null]1New York, NY36973S[null]45.00001
500.000000maleB94[null]1[null]112059S11040.00000
5193.500000maleB69[null]1Montreal, PQ12749S30755.00011
5242.500000maleB11[null]1London / Middlesex113038S[null]42.00000
5351.862500maleE46[null]1Brighton, MA17463S[null][null]00
5450.000000maleC39[null]1London / Birmingham680S[null]55.00000
5552.000000male[null][null]1New York, NY113789S3842.00001
5630.695800male[null]141New York, NYPC 17600C[null][null]00
5728.712500femaleC49[null]1Paris, France New York, NYPC 17595C[null]50.00000
5826.000000male[null][null]1Bennington, VT694S8046.00000
5926.000000maleE60[null]1London113044S[null]50.00000
60211.500000maleC132[null]1[null]113503C4532.50000
6129.700000maleB37[null]1Buffalo, NY11771C25858.00000
6251.862500maleD21[null]1Southington / Noank, CT17464S[null]41.00001
6326.550000maleC124[null]1Portland, OR113028S[null][null]00
6427.720800male[null][null]1Chicago, ILPC 17612C[null][null]00
6530.000000maleD6[null]1Springfield, MA113501S12629.00000
6645.500000male[null][null]1London / New York, NY113801S[null]30.00000
6726.000000maleC106[null]1Brockton, MA110469S[null]30.00000
6853.100000maleD30[null]1New York, NY113773S[null]19.00001
6975.241700maleC6[null]1Vancouver, BC13050C29246.00000
7051.862500maleE46[null]1Dorchester, MA17463S17554.00000
7182.170800male[null][null]1New York, NYPC 17604C[null]28.00001
7226.550000maleE38[null]1East Bridgewater, MA13509S24965.00000
7390.000000maleC78[null]1Fond du Lac, WI19928Q23044.00002
7430.500000maleC30[null]1Montreal, PQ113787S[null]55.00000
7542.400000male[null][null]1Washington, DC113796S[null]47.00000
7629.700000maleC118[null]1Brooklyn, NYPC 17596C[null]37.00010
77113.275000maleD48[null]1Lexington, MA35273C12258.00020
7826.000000male[null][null]1Isle of Wight, England693S26364.00000
7961.979200maleB30[null]1Providence, RI113509C23465.00010
8027.720800maleD43[null]1?Havana, CubaPC 17562C18928.50000
810.000000male[null][null]1Belfast112052S[null][null]00
8228.500000maleC124[null]1Surbiton Hill, Surrey113043S16645.50000
8393.500000maleB24[null]1Montreal, PQ12749S[null]23.00000
8466.600000maleC2[null]1Isleworth, England113776S[null]29.00001
85108.900000maleC65[null]1Madrid, SpainPC 17758C[null]18.00001
8652.000000maleC110[null]1Worcester, MA110465S20747.00000
870.000000male[null][null]1Rotterdam, Netherlands19972S[null]38.00000
88135.633300male[null][null]1[null]PC 17760C23222.00000
89227.525000male[null][null]1[null]PC 17757C[null][null]00
9050.495800maleA24[null]1Trenton, NJPC 17590S[null]31.00000
9150.000000maleA32[null]1Seattle, WA113767S[null][null]00
9240.125000maleA10[null]1Winnipeg, MB13049C[null]36.00000
9359.400000male[null][null]1New York, NYPC 17603C[null]55.00001
9426.550000male[null][null]1London113790S10933.00000
95262.375000maleB57 B59 B63 B66[null]1Haverford, PA / Cooperstown, NYPC 17608C[null]61.00031
9655.900000maleE44[null]1Duluth, MN13507S[null]50.00001
9726.550000male[null][null]1New York, NY113792S[null]56.00000
9830.695800maleA7[null]1St James, Long Island, NY17764C[null]56.00000
9960.000000maleC31[null]1Huntington, WV13695S[null]24.00001
10026.000000maleA19[null]1Streatham, Surrey113056S[null][null]00
Out[6]:
Rows: 1-100 of 1234 | Columns: 14
In [7]:
# Inserts a new file in an existing table
read_csv("titanic.csv", 
         table_name = "titanic_dataset",
         schema = "public",
         quotechar = '"',
         sep = ",",
         na_rep = "",
         parse_n_lines = 100,
         insert = True)
123
fare
Numeric(10,5)
123
survived
Int
Abc
sex
Varchar(20)
Abc
cabin
Varchar(22)
Abc
boat
Varchar(100)
123
pclass
Int
Abc
home.dest
Varchar(92)
Abc
ticket
Varchar(22)
Abc
embarked
Varchar(20)
123
body
Int
123
age
Numeric(5,3)
123
parch
Int
Abc
Varchar(130)
123
sibsp
Int
1151.550000femaleC22 C26[null]1Montreal, PQ / Chesterville, ON113781S[null]2.00021
2151.550000maleC22 C26[null]1Montreal, PQ / Chesterville, ON113781S13530.00021
3151.550000femaleC22 C26[null]1Montreal, PQ / Chesterville, ON113781S[null]25.00021
40.000000maleA36[null]1Belfast, NI112050S[null]39.00000
549.504200male[null][null]1Montevideo, UruguayPC 17609C2271.00000
6227.525000maleC62 C64[null]1New York, NYPC 17757C12447.00001
725.925000male[null][null]1New York, NYPC 17318S[null][null]00
8247.520800maleB58 B60[null]1Montreal, PQPC 17558C[null]24.00010
975.241700maleC6A1Winnipeg, MN13050C[null]36.00000
1026.000000male[null][null]1San Francisco, CA13905C14825.00000
1135.500000maleT[null]1Trenton, NJ113784S[null]45.00000
1226.550000maleD22[null]1London / Winnipeg, MB110489S[null]42.00000
1330.500000maleA21[null]1Pomeroy, WA113054S[null]41.00000
1450.495800maleB10[null]1Omaha, NEPC 17591C20848.00000
1539.600000male[null][null]1Philadelphia, PA112379C[null][null]00
1626.550000maleB38[null]1Washington, DC113050S[null]45.00000
1731.000000male[null][null]1[null]113798S[null][null]00
185.000000maleB51 B53 B55[null]1New York, NY695S[null]33.00000
1947.100000male[null][null]1Montevideo, Uruguay113059S[null]28.00000
2047.100000male[null][null]1Montevideo, Uruguay113059S[null]17.00000
2126.000000male[null][null]1Ascot, Berkshire / Rochester, NY19924S[null]49.00000
2278.850000maleC46[null]1Little Onn Hall, Staffs19877S17236.00001
2361.175000maleE31[null]1Amenia, NDW.E.P. 5734S[null]46.00001
240.000000male[null][null]1Liverpool, England / Belfast112051S[null][null]00
25136.779200maleC89[null]1Los Angeles, CA13508C[null]27.00001
2652.000000maleA14[null]1Stoughton, MA110465S[null][null]00
2725.587500maleE58[null]1Victoria, BC5727S[null]47.00000
2883.158300maleE52[null]1Lakewood, NJPC 17756C[null]37.00011
2926.550000male[null][null]1Roachdale, IN113791S[null][null]00
3071.000000maleB22[null]1Milwaukee, WIWE/P 5735S26970.00011
3171.283300maleC85[null]1New York, NYPC 17599C[null]39.00001
3252.000000maleB71[null]1Montreal, PQF.C. 12750S[null]31.00001
33106.425000maleC86[null]1Deephaven, MN / Cedar Rapids, IAPC 17761C6250.00001
3429.700000maleA18[null]1Philadelphia, PAPC 17580C13339.00000
3531.679200femaleA29[null]1New York, NYPC 17531C[null]36.00000
36221.779200maleC95[null]1[null]PC 17483S[null][null]00
3727.750000maleC111[null]1New York, NY113051C[null]30.00000
38263.000000maleC23 C25 C27[null]1Winnipeg, MB19950S[null]19.00023
39263.000000maleC23 C25 C27[null]1Winnipeg, MB19950S[null]64.00041
4026.550000maleD34[null]1Westcliff-on-Sea, Essex113778S[null][null]00
410.000000maleB102[null]1[null]112058S[null][null]00
4253.100000maleC123[null]1Scituate, MA113803S[null]37.00001
4338.500000maleE63[null]1St Anne's-on-Sea, Lancashire111320S27547.00000
4479.200000maleB86[null]1[null]PC 17593C[null]24.00000
4534.654200maleA5[null]1New York, NYPC 17754C[null]71.00000
46153.462500maleC91[null]1Winnipeg, MBPC 17582S14738.00010
4779.200000maleB82 B84[null]1New York, NYPC 17593C[null]46.00000
4842.400000male[null][null]1[null]113796S[null][null]00
4983.475000maleC83[null]1New York, NY36973S[null]45.00001
500.000000maleB94[null]1[null]112059S11040.00000
5193.500000maleB69[null]1Montreal, PQ12749S30755.00011
5242.500000maleB11[null]1London / Middlesex113038S[null]42.00000
5351.862500maleE46[null]1Brighton, MA17463S[null][null]00
5450.000000maleC39[null]1London / Birmingham680S[null]55.00000
5552.000000male[null][null]1New York, NY113789S3842.00001
5630.695800male[null]141New York, NYPC 17600C[null][null]00
5728.712500femaleC49[null]1Paris, France New York, NYPC 17595C[null]50.00000
5826.000000male[null][null]1Bennington, VT694S8046.00000
5926.000000maleE60[null]1London113044S[null]50.00000
60211.500000maleC132[null]1[null]113503C4532.50000
6129.700000maleB37[null]1Buffalo, NY11771C25858.00000
6251.862500maleD21[null]1Southington / Noank, CT17464S[null]41.00001
6326.550000maleC124[null]1Portland, OR113028S[null][null]00
6427.720800male[null][null]1Chicago, ILPC 17612C[null][null]00
6530.000000maleD6[null]1Springfield, MA113501S12629.00000
6645.500000male[null][null]1London / New York, NY113801S[null]30.00000
6726.000000maleC106[null]1Brockton, MA110469S[null]30.00000
6853.100000maleD30[null]1New York, NY113773S[null]19.00001
6975.241700maleC6[null]1Vancouver, BC13050C29246.00000
7051.862500maleE46[null]1Dorchester, MA17463S17554.00000
7182.170800male[null][null]1New York, NYPC 17604C[null]28.00001
7226.550000maleE38[null]1East Bridgewater, MA13509S24965.00000
7390.000000maleC78[null]1Fond du Lac, WI19928Q23044.00002
7430.500000maleC30[null]1Montreal, PQ113787S[null]55.00000
7542.400000male[null][null]1Washington, DC113796S[null]47.00000
7629.700000maleC118[null]1Brooklyn, NYPC 17596C[null]37.00010
77113.275000maleD48[null]1Lexington, MA35273C12258.00020
7826.000000male[null][null]1Isle of Wight, England693S26364.00000
7961.979200maleB30[null]1Providence, RI113509C23465.00010
8027.720800maleD43[null]1?Havana, CubaPC 17562C18928.50000
810.000000male[null][null]1Belfast112052S[null][null]00
8228.500000maleC124[null]1Surbiton Hill, Surrey113043S16645.50000
8393.500000maleB24[null]1Montreal, PQ12749S[null]23.00000
8466.600000maleC2[null]1Isleworth, England113776S[null]29.00001
85108.900000maleC65[null]1Madrid, SpainPC 17758C[null]18.00001
8652.000000maleC110[null]1Worcester, MA110465S20747.00000
870.000000male[null][null]1Rotterdam, Netherlands19972S[null]38.00000
88135.633300male[null][null]1[null]PC 17760C23222.00000
89227.525000male[null][null]1[null]PC 17757C[null][null]00
9050.495800maleA24[null]1Trenton, NJPC 17590S[null]31.00000
9150.000000maleA32[null]1Seattle, WA113767S[null][null]00
9240.125000maleA10[null]1Winnipeg, MB13049C[null]36.00000
9359.400000male[null][null]1New York, NYPC 17603C[null]55.00001
9426.550000male[null][null]1London113790S10933.00000
95262.375000maleB57 B59 B63 B66[null]1Haverford, PA / Cooperstown, NYPC 17608C[null]61.00031
9655.900000maleE44[null]1Duluth, MN13507S[null]50.00001
9726.550000male[null][null]1New York, NY113792S[null]56.00000
9830.695800maleA7[null]1St James, Long Island, NY17764C[null]56.00000
9960.000000maleC31[null]1Huntington, WV13695S[null]24.00001
10026.000000maleA19[null]1Streatham, Surrey113056S[null][null]00
Out[7]:
Rows: 1-100 of 2468 | Columns: 14

See Also

read_json Ingests a JSON file in the Vertica DB.