- Download the product delivery file.
- Extract the delivery file to find the text data file.
- Create the table structure in the database using the Create Table script. Be sure to change the table name in the template to data_link_geox.
- Once the table structure has been created, copy data using the PostgreSQL Copy command:
copy
<table_name> from '<path_to_text_file>\<text_file_name.txt>'
delimiter '|' csv header;Syntax:
<table_name>: Name of table created using Create Table script.
<path_to_text_file>: Path to extracted data text file.
<text_file_name.txt>: Extracted data text file name, including file extension.
delimiter: Defines the text delimiter (in this case, the pipe character).
Load the State-wise file example:
- data_link_for_geox_AK.txt
- data_link_for_geox_AL.txt
Example – Linux:
{{copy data_link_geox
from '/mnr/nationwide/geox_precisely_data/data_link_for_geox_{StateCode}.txt' delimiter '|' csv header;}}Example – Windows:
{{copy data_link_geox
from 'F:\nationwide\geox_precisely_data\data_link_for_geox_{State_Code}.txt' delimiter '|' csv header;}}Note: If any errors:
- Invalid input syntax for type numeric: ""mydb=# \copy data_link_geox FROM 'data_link_for_geox_ak.txt' WITH (FORMAT csv, DELIMITER '|', HEADER true, NULL '')
- Invalid input syntax for type numeric: ""CONTEXT: COPY data_link_geox, line 2, column ground_height_amsl""
- Use this as Force_Null
(solution):
\copy data_link_geox FROM 'data_link_for_geox_ak.txt' WITH (FORMAT csv, DELIMITER '|', HEADER true, NULL '',FORCE_NULL (ground_height_amsl,building_height,number_of_stories,square_footage));