Differences

This shows you the differences between two versions of the page.

--- notes:postgres [2015/08/11 14:18]
jiggak
+++ notes:postgres [2015/08/17 14:05] (current)
jiggak
@@ Line 3: / Line 3: @@
 create role josh with login;
 create database edc owner josh;
+</code>
+<code>
+bcp "[Event]" out Event.data -S (localdb)\Projects -T -d EDC -c -t 0xfe -r 0xf7 -C RAW -k
 </code>
 <code bash>
--- run bcp in windows cmd to dump table
+#!/bin/bash
-bcp Campaign out campaign.data -c -k -S (localdb)\Projects -T -d EDC
--- convert line endings to unix
+for f in *.data; do
-dos2unix campaign.data
+	echo $f
--- import data with psql
+	cat $f | \
-copy campaign from '/media/sf_D_DRIVE/pg_migrate/campaign.data' with null as '';
+		sed 's/\xf7/|\n|/g' | \
+		# remove extra at end of file
+		sed '$ s/.$//' | \
+		# add missing at start of file
+		sed '1 s/^/|/' | \
+		sed 's/"/""/g' | \
+		sed 's/^|\||$/"/g' | \
+		sed 's/\xfe/","/g' | \
+		sed 's/,"",/,,/g' | \
+		sed 's/,"",/,,/g' | \
+		sed 's/,""$/,/g' | \
+		sed 's/\\/\\\\/g' | \
+		# dos2unix identified these non-char bytes
+		tr -d '\31\20\16\0' | \
+		# dos2unix | \
+		iconv -f WINDOWS-1252 -t UTF8 > $f.csv
+done
+</code>
+<code sql>
+\copy event (id,name,partner_id,city_id,module_id,region_id,start_date,dyno,loc_code,event_type_id,event_code,end_date,address,postal_code,max_pre_reg,lat,lng,directions_url,pre_reg_close_time,sms_message,private,campaign_id) from 'Event.data.csv' csv
+select setval('event_id_seq', (select max(id) from event));
 </code>

Slashdev Wiki