Differences

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

--- notes:postgres [2015/08/11 14:19]
jiggak
+++ notes:postgres [2015/08/17 14:05] (current)
jiggak
@@ Line 6: / Line 6: @@
 <code>
--- run bcp in windows cmd to dump table
+bcp "[Event]" out Event.data -S (localdb)\Projects -T -d EDC -c -t 0xfe -r 0xf7 -C RAW -k
-bcp Campaign out campaign.data -c -k -S (localdb)\Projects -T -d EDC
+</code>
--- convert line endings to unix
-dos2unix campaign.data
+<code bash>
--- import data with psql
+#!/bin/bash
-copy campaign from '/media/sf_D_DRIVE/pg_migrate/campaign.data' with null as '';
+for f in *.data; do
+	echo $f
+	cat $f | \
+		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