Results 1 to 8 of 8
  1. #1
    Join Date
    May 2014
    Posts
    20

    Red face Unanswered: HELP create table from multiple CSV files

    Hi,
    I am new to PSQL and using it for storing the data collected from another proprietary program that stores data collected from an HVAC system to CSV files of 3600 rows. I wanted to make it more accessible by migrating those csv data to one single db on psql. I used the create table command but am getting an error that I need help with.
    The script is
    create table ghp1 (date varchar(255),time varchar(255),unknown varchar(255),E_G_OP_SW varchar(255),HP_SW varchar(255),LP_SW varchar(255),High_pressure varchar(255),Low_pressure varchar(255),HP_equiv_temp varchar(255),.....
    ....
    varchar(255),Field_108 varchar(255))

    and getting this error

    invalid byte sequence for encoding "UTF8": 0x8d

    How to fix this error. I am new to PSQL and was not able to fix using the documentation and other documents on site.

  2. #2
    Join Date
    Apr 2014
    Posts
    16
    Are you getting an error while running the create table script?

    Can you type the whole script?

  3. #3
    Join Date
    May 2014
    Posts
    20
    Yes I get this error

    invalid byte sequence for encoding "UTF8": 0x8d

    for the query
    create table ghp1 (date varchar(255),time varchar(255),unknown varchar(255),E_G_OP_SW varchar(255),HP_SW varchar(255),LP_SW varchar(255),High_pressure varchar(255),Low_pressure varchar(255),HP_equiv_temp varchar(255),LP_equiv_temp varchar(255),C_P_1_discharge_temp varchar(255),C_P_2_discharge_temp varchar(255),C_P_3_discharge_temp varchar(255),C_P_4_discharge_temp varchar(255),C_P_intake_temp_1 varchar(255),C_P_intake_temp_2 varchar(255),Liquid_pipe_temp varchar(255),Heat_ex_liquid_temp varchar(255),Sub_heat_ex_outlet_temp varchar(255),Accumulator_outlet_temp_1 varchar(255),Accumulator_outlet_temp_2 varchar(255),Supercooling_heat_ex_intake_temp varchar(255),Data_spare_1 varchar(255),Data_spare_2 varchar(255),Data_spare_3 varchar(255),Outside_temp varchar(255),E_G_oil_temp varchar(255),E_G_room_temp varchar(255),E_G_exhaust_air_temp varchar(255),Starter_voltage varchar(255),Igniter_voltage varchar(255),Fuel_gas_valve_output_check_input varchar(255),Power_supply_phase varchar(255),RS_phase_detection varchar(255),ST_phase_detection varchar(255),TR_phase_detection varchar(255),E_G_side_spare_input varchar(255),Silent_mode_external_input varchar(255),Input_spare_1 varchar(255),Input_spare_2 varchar(255),Compressor_1 varchar(255),Compressor_2 varchar(255),Compressor_3 varchar(255),Compressor_4 varchar(255),4way_chgov_valve varchar(255),Output_spare_1 varchar(255),Fan_airflow_level varchar(255),Heat_ex_fan_1 varchar(255),Heat_ex_fan_2 varchar(255),Heat_ex_fan_3 varchar(255),E_G_coolant_pump varchar(255),Throttle_valve varchar(255),Fuel_gas_valve varchar(255),Main_heat_ex_refrig_liquid_flow_valve varchar(255),Sub_heat_ex_refrig_liquid_flow_valve varchar(255),Supercooling_valve varchar(255),C_P_capacity_control_valve varchar(255),Liquid_flow_control_valve varchar(255),Data_spare_4 varchar(255),Compressor_heater varchar(255),Drain_heater varchar(255),Oil_pan_heater varchar(255),Fuel_gas_valve_1 varchar(255),Fuel_gas_valve_2 varchar(255),Starter varchar(255),Starter_transformer varchar(255),12_VDC_output_permission_iMainj varchar(255),12_VDC_output_permission_iE_Gj varchar(255),Output_data_spare_1 varchar(255),Output_data_spare_2 varchar(255),Specified_engine_RPM varchar(255),Actual_engine_RPM varchar(255),Engine_igniter_spark varchar(255),Requested_calculated_engine_RPM_total varchar(255),Calculated_engine_RPM_total varchar(255),Requested_calculated_engine_RPM varchar(255),Calculated_engine_RPM varchar(255),Suction_superheating_degree varchar(255),Sub_heat_ex_outlet_superheating_degre e varchar(255),Outdoor_supercooling_degree varchar(255),Outdoor_heat_ex_supercooling_degree varchar(255),Data_spare_5 varchar(255),Data_spare_6 varchar(255),Silent_mode varchar(255),Compressor_protect_control varchar(255),Defrost_control varchar(255),Cold_district_spec_operation varchar(255),Periodic_inspection varchar(255),E_G_operation_hour varchar(255),Requesting_power_generation_amount varchar(255),Actual_power_generation varchar(255),Oil_return_valve_1 varchar(255),Oil_return_valve_2 varchar(255),Oil_return_valve_3 varchar(255),Oil_return_valve_4 varchar(255),Hot_gas_bypass_valve varchar(255),Output_spare_4 varchar(255),Output_spare_5 varchar(255),Refrig_gas_P_supply_valve varchar(255),Output_spare_9 varchar(255),Oil_return_valve varchar(255),Refrig_gas_valve varchar(255),Output_spare_2 varchar(255),Output_spare_3 varchar(255),Output_spare_6 varchar(255),Output_spare_7 varchar(255),Output_spare_8 varchar(255),Output_spare_10 varchar(255),Field_108 varchar(255))create table ghp1 (date varchar(255),time varchar(255),unknown varchar(255),E_G_OP_SW varchar(255),HP_SW varchar(255),LP_SW varchar(255),High_pressure varchar(255),Low_pressure varchar(255),HP_equiv_temp varchar(255),LP_equiv_temp varchar(255),C_P_1_discharge_temp varchar(255),C_P_2_discharge_temp varchar(255),C_P_3_discharge_temp varchar(255),C_P_4_discharge_temp varchar(255),C_P_intake_temp_1 varchar(255),C_P_intake_temp_2 varchar(255),Liquid_pipe_temp varchar(255),Heat_ex_liquid_temp varchar(255),Sub_heat_ex_outlet_temp varchar(255),Accumulator_outlet_temp_1 varchar(255),Accumulator_outlet_temp_2 varchar(255),Supercooling_heat_ex_intake_temp varchar(255),Data_spare_1 varchar(255),Data_spare_2 varchar(255),Data_spare_3 varchar(255),Outside_temp varchar(255),E_G_oil_temp varchar(255),E_G_room_temp varchar(255),E_G_exhaust_air_temp varchar(255),Starter_voltage varchar(255),Igniter_voltage varchar(255),Fuel_gas_valve_output_check_input varchar(255),Power_supply_phase varchar(255),RS_phase_detection varchar(255),ST_phase_detection varchar(255),TR_phase_detection varchar(255),E_G_side_spare_input varchar(255),Silent_mode_external_input varchar(255),Input_spare_1 varchar(255),Input_spare_2 varchar(255),Compressor_1 varchar(255),Compressor_2 varchar(255),Compressor_3 varchar(255),Compressor_4 varchar(255),4way_chgov_valve varchar(255),Output_spare_1 varchar(255),Fan_airflow_level varchar(255),Heat_ex_fan_1 varchar(255),Heat_ex_fan_2 varchar(255),Heat_ex_fan_3 varchar(255),E_G_coolant_pump varchar(255),Throttle_valve varchar(255),Fuel_gas_valve varchar(255),Main_heat_ex_refrig_liquid_flow_valve varchar(255),Sub_heat_ex_refrig_liquid_flow_valve varchar(255),Supercooling_valve varchar(255),C_P_capacity_control_valve varchar(255),Liquid_flow_control_valve varchar(255),Data_spare_4 varchar(255),Compressor_heater varchar(255),Drain_heater varchar(255),Oil_pan_heater varchar(255),Fuel_gas_valve_1 varchar(255),Fuel_gas_valve_2 varchar(255),Starter varchar(255),Starter_transformer varchar(255),12_VDC_output_permission_iMainj varchar(255),12_VDC_output_permission_iE_Gj varchar(255),Output_data_spare_1 varchar(255),Output_data_spare_2 varchar(255),Specified_engine_RPM varchar(255),Actual_engine_RPM varchar(255),Engine_igniter_spark varchar(255),Requested_calculated_engine_RPM_total varchar(255),Calculated_engine_RPM_total varchar(255),Requested_calculated_engine_RPM varchar(255),Calculated_engine_RPM varchar(255),Suction_superheating_degree varchar(255),Sub_heat_ex_outlet_superheating_degre e varchar(255),Outdoor_supercooling_degree varchar(255),Outdoor_heat_ex_supercooling_degree varchar(255),Data_spare_5 varchar(255),Data_spare_6 varchar(255),Silent_mode varchar(255),Compressor_protect_control varchar(255),Defrost_control varchar(255),Cold_district_spec_operation varchar(255),Periodic_inspection varchar(255),E_G_operation_hour varchar(255),Requesting_power_generation_amount varchar(255),Actual_power_generation varchar(255),Oil_return_valve_1 varchar(255),Oil_return_valve_2 varchar(255),Oil_return_valve_3 varchar(255),Oil_return_valve_4 varchar(255),Hot_gas_bypass_valve varchar(255),Output_spare_4 varchar(255),Output_spare_5 varchar(255),Refrig_gas_P_supply_valve varchar(255),Output_spare_9 varchar(255),Oil_return_valve varchar(255),Refrig_gas_valve varchar(255),Output_spare_2 varchar(255),Output_spare_3 varchar(255),Output_spare_6 varchar(255),Output_spare_7 varchar(255),Output_spare_8 varchar(255),Output_spare_10 varchar(255),Field_108 varchar(255))

    I created a blank db and tried to run this and get this error

  4. #4
    Join Date
    May 2014
    Posts
    20
    The query that gives me error on a blank db is
    error is
    invalid byte sequence for encoding "UTF8": 0x81

    create table ghp1 (date varchar(255),time varchar(255),unknown varchar(255),E_G_OP_SW varchar(255),HP_SW varchar(255),LP_SW varchar(255),High_pressure varchar(255),Low_pressure varchar(255),HP_equiv_temp varchar(255),LP_equiv_temp varchar(255),C_P_1_discharge_temp varchar(255),C_P_2_discharge_temp varchar(255),C_P_3_discharge_temp varchar(255),C_P_4_discharge_temp varchar(255),C_P_intake_temp_1 varchar(255),C_P_intake_temp_2 varchar(255),Liquid_pipe_temp varchar(255),Heat_ex_liquid_temp varchar(255),Sub_heat_ex_outlet_temp varchar(255),Accumulator_outlet_temp_1 varchar(255),Accumulator_outlet_temp_2 varchar(255),Supercooling_heat_ex_intake_temp varchar(255),Data_spare_1 varchar(255),Data_spare_2 varchar(255),Data_spare_3 varchar(255),Outside_temp varchar(255),E_G_oil_temp varchar(255),E_G_room_temp varchar(255),E_G_exhaust_air_temp varchar(255),Starter_voltage varchar(255),Igniter_voltage varchar(255),Fuel_gas_valve_output_check_input varchar(255),Power_supply_phase varchar(255),RS_phase_detection varchar(255),ST_phase_detection varchar(255),TR_phase_detection varchar(255),E_G_side_spare_input varchar(255),Silent_mode_external_input varchar(255),Input_spare_1 varchar(255),Input_spare_2 varchar(255),Compressor_1 varchar(255),Compressor_2 varchar(255),Compressor_3 varchar(255),Compressor_4 varchar(255),fourway_chgov_valve varchar(255),Output_spare_1 varchar(255),Fan_airflow_level varchar(255),Heat_ex_fan_1 varchar(255),Heat_ex_fan_2 varchar(255),Heat_ex_fan_3 varchar(255),E_G_coolant_pump varchar(255),Throttle_valve varchar(255),Fuel_gas_valve varchar(255),Main_heat_ex_refrig_liquid_flow_valve varchar(255),Sub_heat_ex_refrig_liquid_flow_valve varchar(255),Supercooling_valve varchar(255),C_P_capacity_control_valve varchar(255),Liquid_flow_control_valve varchar(255),Data_spare_4 varchar(255),Compressor_heater varchar(255),Drain_heater varchar(255),Oil_pan_heater varchar(255),Fuel_gas_valve_1 varchar(255),Fuel_gas_valve_2 varchar(255),Starter varchar(255),Starter_transformer varchar(255),12_VDC_output_permission_iMainj varchar(255),12_VDC_output_permission_iE_Gj varchar(255),Output_data_spare_1 varchar(255),Output_data_spare_2 varchar(255),Specified_engine_RPM varchar(255),Actual_engine_RPM varchar(255),Engine_igniter_spark varchar(255),Requested_calculated_engine_RPM_total varchar(255),Calculated_engine_RPM_total varchar(255),Requested_calculated_engine_RPM varchar(255),Calculated_engine_RPM varchar(255),Suction_superheating_degree varchar(255),Sub_heat_ex_outlet_superheating_degre e varchar(255),Outdoor_supercooling_degree varchar(255),Outdoor_heat_ex_supercooling_degree varchar(255),Data_spare_5 varchar(255),Data_spare_6 varchar(255),Silent_mode varchar(255),Compressor_protect_control varchar(255),Defrost_control varchar(255),Cold_district_spec_operation varchar(255),Periodic_inspection varchar(255),E_G_operation_hour varchar(255),Requesting_power_generation_amount varchar(255),Actual_power_generation varchar(255),Oil_return_valve_1 varchar(255),Oil_return_valve_2 varchar(255),Oil_return_valve_3 varchar(255),Oil_return_valve_4 varchar(255),Hot_gas_bypass_valve varchar(255),Output_spare_4 varchar(255),Output_spare_5 varchar(255),Refrig_gas_P_supply_valve varchar(255),Output_spare_9 varchar(255),Oil_return_valve varchar(255),Refrig_gas_valve varchar(255),Output_spare_2 varchar(255),Output_spare_3 varchar(255),Output_spare_6 varchar(255),Output_spare_7 varchar(255),Output_spare_8 varchar(255),Output_spare_10 varchar(255),Field_108 varchar(255))create table ghp1 (date varchar(255),time varchar(255),unknown varchar(255),E_G_OP_SW varchar(255),HP_SW varchar(255),LP_SW varchar(255),High_pressure varchar(255),Low_pressure varchar(255),HP_equiv_temp varchar(255),LP_equiv_temp varchar(255),C_P_1_discharge_temp varchar(255),C_P_2_discharge_temp varchar(255),C_P_3_discharge_temp varchar(255),C_P_4_discharge_temp varchar(255),C_P_intake_temp_1 varchar(255),C_P_intake_temp_2 varchar(255),Liquid_pipe_temp varchar(255),Heat_ex_liquid_temp varchar(255),Sub_heat_ex_outlet_temp varchar(255),Accumulator_outlet_temp_1 varchar(255),Accumulator_outlet_temp_2 varchar(255),Supercooling_heat_ex_intake_temp varchar(255),Data_spare_1 varchar(255),Data_spare_2 varchar(255),Data_spare_3 varchar(255),Outside_temp varchar(255),E_G_oil_temp varchar(255),E_G_room_temp varchar(255),E_G_exhaust_air_temp varchar(255),Starter_voltage varchar(255),Igniter_voltage varchar(255),Fuel_gas_valve_output_check_input varchar(255),Power_supply_phase varchar(255),RS_phase_detection varchar(255),ST_phase_detection varchar(255),TR_phase_detection varchar(255),E_G_side_spare_input varchar(255),Silent_mode_external_input varchar(255),Input_spare_1 varchar(255),Input_spare_2 varchar(255),Compressor_1 varchar(255),Compressor_2 varchar(255),Compressor_3 varchar(255),Compressor_4 varchar(255),4way_chgov_valve varchar(255),Output_spare_1 varchar(255),Fan_airflow_level varchar(255),Heat_ex_fan_1 varchar(255),Heat_ex_fan_2 varchar(255),Heat_ex_fan_3 varchar(255),E_G_coolant_pump varchar(255),Throttle_valve varchar(255),Fuel_gas_valve varchar(255),Main_heat_ex_refrig_liquid_flow_valve varchar(255),Sub_heat_ex_refrig_liquid_flow_valve varchar(255),Supercooling_valve varchar(255),C_P_capacity_control_valve varchar(255),Liquid_flow_control_valve varchar(255),Data_spare_4 varchar(255),Compressor_heater varchar(255),Drain_heater varchar(255),Oil_pan_heater varchar(255),Fuel_gas_valve_1 varchar(255),Fuel_gas_valve_2 varchar(255),Starter varchar(255),Starter_transformer varchar(255),12_VDC_output_permission_iMainj varchar(255),12_VDC_output_permission_iE_Gj varchar(255),Output_data_spare_1 varchar(255),Output_data_spare_2 varchar(255),Specified_engine_RPM varchar(255),Actual_engine_RPM varchar(255),Engine_igniter_spark varchar(255),Requested_calculated_engine_RPM_total varchar(255),Calculated_engine_RPM_total varchar(255),Requested_calculated_engine_RPM varchar(255),Calculated_engine_RPM varchar(255),Suction_superheating_degree varchar(255),Sub_heat_ex_outlet_superheating_degre e varchar(255),Outdoor_supercooling_degree varchar(255),Outdoor_heat_ex_supercooling_degree varchar(255),Data_spare_5 varchar(255),Data_spare_6 varchar(255),Silent_mode varchar(255),Compressor_protect_control varchar(255),Defrost_control varchar(255),Cold_district_spec_operation varchar(255),Periodic_inspection varchar(255),E_G_operation_hour varchar(255),Requesting_power_generation_amount varchar(255),Actual_power_generation varchar(255),Oil_return_valve_1 varchar(255),Oil_return_valve_2 varchar(255),Oil_return_valve_3 varchar(255),Oil_return_valve_4 varchar(255),Hot_gas_bypass_valve varchar(255),Output_spare_4 varchar(255),Output_spare_5 varchar(255),Refrig_gas_P_supply_valve varchar(255),Output_spare_9 varchar(255),Oil_return_valve varchar(255),Refrig_gas_valve varchar(255),Output_spare_2 varchar(255),Output_spare_3 varchar(255),Output_spare_6 varchar(255),Output_spare_7 varchar(255),Output_spare_8 varchar(255),Output_spare_10 varchar(255),Field_108 varchar(255))

  5. #5
    Join Date
    May 2014
    Posts
    20
    I think the script is long and needs administrator approval and will take time.
    but the error I get is
    invalid byte sequence for encoding "UTF8": 0x81

    attaching the script as attachment
    Attached Files Attached Files

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It appears that the script is being written as RTF and read as UTF-8. The easiest way to work around this is to use an editor that natively writes UTF-8 or possibly Native formatted text files. Paste the text into Windows Notepad or a LInux character mode editor, and you ought to be in business!

    As a side note, this is probably a good "quick and dirty" script, but you need to think more about data types, what lines were rejected why they were rejected. This is probably what you got from the CSV files as input, but it is decidedly NOT normalized data. You should consider this as a starting/transfer point, not a validated set of data to process.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    May 2014
    Posts
    20

    Exclamation

    Thanks for your response. I created this script and pasted on notepad++ and got to run it.
    1. I am not very sure how to size and determine the data types. Can anyone suggest some quick resource to get understanding of these.
    2. The table is created but the next step is to import multiple files copy from csv to the table. I know you can use copy command, but how to use it for multiple files with a file-names starting with mmddyyy_unique_numbers

    Thanks for helping me out. I am trying to import one file at a time but eventually I need to import data from file of about 100 csv generated for each day.

  8. #8
    Join Date
    May 2014
    Posts
    20
    Guys Can anyone help.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •