· 2 years ago · May 08, 2023, 01:30 AM
1// Import required modules
2const mysql = require("mysql");
3const fs = require("fs");
4const csvParser = require("csv-parser");
5
6const headersAndTypes = {
7 '@odata.id': 'VARCHAR(80)',
8 'ListingKey': 'VARCHAR(15)',
9 'OriginatingSystemKey': 'VARCHAR(10)',
10 'BathroomsFull': 'VARCHAR(5)',
11 'BathroomsHalf': 'VARCHAR(5)',
12 'BathroomsTotalInteger': 'VARCHAR(5)',
13 'BedroomsTotal': 'VARCHAR(5)',
14 'BuildingAreaUnits': 'VARCHAR(25)',
15 'BuyerAgencyCompensation': 'VARCHAR(5)',
16 'Cooling': 'VARCHAR(15)',
17 'CountyOrParish': 'VARCHAR(30)',
18 'CumulativeDaysOnMarket': 'VARCHAR(5)',
19 'DaysOnMarket': 'VARCHAR(5)',
20 'Heating': 'VARCHAR(15)',
21 'InternetAddressDisplayYN': 'VARCHAR(5)',
22 'InternetAutomatedValuationDisplayYN': 'VARCHAR(5)',
23 'InternetConsumerCommentYN': 'VARCHAR(5)',
24 'InternetEntireListingDisplayYN': 'VARCHAR(5)',
25 'ListAgentAOR': 'VARCHAR(5)',
26 'ListAgentDirectPhone': 'VARCHAR(20)',
27 'ListAgentEmail': 'VARCHAR(25)',
28 'ListAgentFullName': 'VARCHAR(25)',
29 'ListAgentKey': 'VARCHAR(15)',
30 'ListAgentMlsId': 'VARCHAR(25)',
31 'ListAOR': 'VARCHAR(25)',
32 'ListingAgreement': 'VARCHAR(25)',
33 'ListingContractDate': 'DATETIME',
34 'ListingId': 'VARCHAR(15)',
35 'ListOfficeKey': 'VARCHAR(15)',
36 'ListOfficeMlsId': 'VARCHAR(15)',
37 'ListOfficeName': 'VARCHAR(35)',
38 'ListOfficePhone': 'VARCHAR(15)',
39 'ListPrice': 'VARCHAR(15)',
40 'LivingArea': 'VARCHAR(15)',
41 'LivingAreaSource': 'VARCHAR(15)',
42 'LivingAreaUnits': 'VARCHAR(15)',
43 'MlsStatus': 'VARCHAR(15)',
44 'OriginalEntryTimestamp': 'VARCHAR(25)',
45 'OriginalListPrice': 'VARCHAR(15)',
46 'MFR_OriginatingSystemName_': 'VARCHAR(25)',
47 'ParcelNumber': 'VARCHAR(45)',
48 'PhotosChangeTimestamp': 'VARCHAR(25)',
49 'PhotosCount': 'VARCHAR(5)',
50 'PostalCode': 'VARCHAR(10)',
51 'PostalCodePlus4': 'VARCHAR(5)',
52 'PrivateRemarks': 'VARCHAR(255)',
53 'PropertySubType': 'VARCHAR(25)',
54 'PropertyType': 'VARCHAR(25)',
55 'PublicRemarks': 'TEXT',
56 'StandardStatus': 'VARCHAR(15)',
57 'StateOrProvince': 'VARCHAR(5)',
58 'StatusChangeTimestamp': 'VARCHAR(35)',
59 'TaxYear': 'VARCHAR(5)',
60 'YearBuilt': 'VARCHAR(5)',
61 'MFR_CurrentPrice': 'VARCHAR(15)',
62 'MFR_RoomCount': 'VARCHAR(5)',
63 'SpecialListingConditions': 'VARCHAR(25)',
64 'AdditionalParcelsYN': 'VARCHAR(5)',
65 'Appliances': 'VARCHAR(100)',
66 'MFR_AssociationApprovalRequiredYN': 'VARCHAR(5)',
67 'AttachedGarageYN': 'VARCHAR(5)',
68 'MFR_AvailableForLeaseYN': 'VARCHAR(5)',
69 'CarportYN': 'VARCHAR(5)',
70 'MFR_CDDYN': 'VARCHAR(5)',
71 'ConstructionMaterials': 'VARCHAR(25)',
72 'Country': 'VARCHAR(5)',
73 'DirectionFaces': 'VARCHAR(5)',
74 'MFR_DPRURL': 'VARCHAR(100)',
75 'MFR_DPRURL2': 'VARCHAR(100)',
76 'MFR_DPRYN': 'VARCHAR(5)',
77 'MFR_ExistLseTenantYN': 'VARCHAR(5)',
78 'FireplaceYN': 'VARCHAR(5)',
79 'MFR_FloodZoneCode': 'VARCHAR(5)',
80 'MFR_FloodZoneDate': 'VARCHAR(5)',
81 'MFR_FloodZonePanel': 'VARCHAR(5)',
82 'FoundationDetails': 'VARCHAR(25)',
83 'ExteriorFeatures': 'VARCHAR(100)',
84 'GarageSpaces': 'VARCHAR(5)',
85 'GarageYN': 'VARCHAR(5)',
86 'MFR_HomesteadYN': 'VARCHAR(5)',
87 'Zoning': 'VARCHAR(5)',
88 'MFR_YrsOfOwnerPriorToLeasingReqYN': 'VARCHAR(5)',
89 'MFR_LeaseRestrictionsYN': 'VARCHAR(5)',
90 'ListAgentFax': 'VARCHAR(5)',
91 'ListAgentOfficePhoneExt': 'VARCHAR(5)',
92 'ListAgentPager': 'VARCHAR(15)',
93 'ListOfficeFax': 'VARCHAR(15)',
94 'LotSizeAcres': 'VARCHAR(10)',
95 'LotSizeSquareFeet': 'VARCHAR(10)',
96 'MFR_MinimumLease': 'VARCHAR(15)',
97 'NewConstructionYN': 'VARCHAR(15)',
98 'Ownership': 'VARCHAR(15)',
99 'PoolPrivateYN': 'VARCHAR(5)',
100 'PublicSurveyRange': 'VARCHAR(5)',
101 'PublicSurveySection': 'VARCHAR(5)',
102 'PublicSurveySection': 'VARCHAR(5)',
103 'Roof': 'VARCHAR(15)',
104 'SeniorCommunityYN': 'VARCHAR(5)',
105 'ShowingRequirements': 'VARCHAR(5)',
106 'SubdivisionName': 'VARCHAR(35)',
107 'City': 'VARCHAR(15)',
108 'TaxAnnualAmount': 'VARCHAR(5)',
109 'TaxBlock': 'VARCHAR(5)',
110 'TaxBookNumber': 'VARCHAR(5)',
111 'TaxLegalDescription': 'VARCHAR(5)',
112 'TaxLot': 'VARCHAR(5)',
113 'MFR_TotalAcreage': 'VARCHAR(5)',
114 'Township': 'VARCHAR(5)',
115 'TransactionBrokerCompensation': 'VARCHAR(5)',
116 'Utilities': 'VARCHAR(5)',
117 'MFR_WaterAccessYN': 'VARCHAR(5)',
118 'MFR_WaterExtrasYN': 'VARCHAR(5)',
119 'MFR_WaterfrontFeetTotal': 'VARCHAR(5)',
120 'WaterfrontYN': 'VARCHAR(5)',
121 'WaterSource': 'VARCHAR(5)',
122 'MFR_WaterViewYN': 'VARCHAR(5)',
123 'MLSAreaMajor': 'VARCHAR(5)',
124 'AssociationYN': 'VARCHAR(5)',
125 'InteriorFeatures': 'VARCHAR(5)',
126 'ListingTerms': 'VARCHAR(5)',
127 'Flooring': 'VARCHAR(5)',
128 'Sewer': 'VARCHAR(5)',
129 'PetsAllowed': 'VARCHAR(5)',
130 'MFR_CalculatedListPriceByCalculatedSqFt': 'VARCHAR(5)',
131 'Levels': 'VARCHAR(5)',
132 'MFR_ListOfficeHeadOfficeKeyNumeric': 'VARCHAR(5)',
133 'MFR_NonRepCompensation': 'VARCHAR(5)',
134 'MFR_UnitNumberYN': 'VARCHAR(5)',
135 'MFR_RATIO_CurrentPrice_By_CalculatedSqFt': 'VARCHAR(5)',
136 'MFR_LivingAreaMeters': 'VARCHAR(5)',
137 'MFR_LotSizeSquareMeters': 'VARCHAR(5)',
138 'MFR_SDEOYN': 'VARCHAR(5)',
139 'MFR_PublicRemarksAgent': 'VARCHAR(5)',
140 'MFR_TotalAnnualFees': 'VARCHAR(5)',
141 'MFR_TotalMonthlyFees': 'VARCHAR(5)',
142 'UniversalPropertyId': 'VARCHAR(5)',
143 'MFR_ListingExclusionYN': 'VARCHAR(5)',
144 'OccupantType': 'VARCHAR(5)',
145 'RoadSurfaceType': 'VARCHAR(5)',
146 'MFR_EscrowAgentEmail': 'VARCHAR(5)',
147 'MFR_EscrowAgentName': 'VARCHAR(5)',
148 'MFR_EscrowAgentPhone': 'VARCHAR(5)',
149 'MFR_EscrowCity': 'VARCHAR(5)',
150 'MFR_EscrowCompany': 'VARCHAR(5)',
151 'MFR_EscrowPostalCode': 'VARCHAR(5)',
152 'MFR_EscrowState': 'VARCHAR(5)',
153 'MFR_EscrowStreetName': 'VARCHAR(5)',
154 'MFR_EscrowStreetNumber': 'VARCHAR(5)',
155 'OnMarketDate': 'VARCHAR(5)',
156 'OriginatingSystemName': 'VARCHAR(5)',
157 'ModificationTimestamp': 'VARCHAR(5)',
158 'Directions': 'VARCHAR(5)',
159 'Latitude': 'VARCHAR(5)',
160 'Longitude': 'VARCHAR(5)',
161 'MFR_SWSubdivCommunityName': 'VARCHAR(5)',
162 'StreetName': 'VARCHAR(5)',
163 'StreetNumber': 'VARCHAR(5)',
164 'StreetSuffix': 'VARCHAR(5)',
165 'UnparsedAddress': 'VARCHAR(5)',
166 'MlgCanView': 'VARCHAR(5)',
167 'MlgCanUse': 'VARCHAR(5)',
168 'BuildingAreaSource': 'VARCHAR(5)',
169 'BuildingAreaTotal': 'VARCHAR(5)',
170 'StoriesTotal': 'VARCHAR(5)',
171 'AssociationFee': 'VARCHAR(5)',
172 'AssociationName': 'VARCHAR(5)',
173 'AssociationPhone': 'VARCHAR(5)',
174 'AssociationFeeFrequency': 'VARCHAR(5)',
175 'Furnished': 'VARCHAR(5)',
176 'MFR_MonthlyHOAAmount': 'VARCHAR(5)',
177 'ParkingFeatures': 'VARCHAR(5)',
178 'PoolFeatures': 'VARCHAR(5)',
179 'MFR_RealtorInfo': 'VARCHAR(5)',
180 'SpaFeatures': 'VARCHAR(5)',
181 'TaxOtherAnnualAssessmentAmount': 'VARCHAR(5)',
182 'VirtualTourURLUnbranded': 'VARCHAR(5)',
183 'PatioAndPorchFeatures': 'VARCHAR(5)',
184 'SpaYN': 'VARCHAR(5)',
185 'MFR_BuildingAreaTotalSrchSqM': 'VARCHAR(5)',
186 'MFR_AssociationURL': 'VARCHAR(5)',
187 'MFR_GreenVerificationCount': 'VARCHAR(5)',
188 'MFR_ListOfficeContactPreferred': 'VARCHAR(5)',
189 'MFR_AssociationFeeRequirement': 'VARCHAR(5)',
190 'PreviousListPrice': 'VARCHAR(5)',
191 'PriceChangeTimestamp': 'VARCHAR(5)',
192 'MFR_AdditionalLeaseRestrictions': 'VARCHAR(5)',
193 'AssociationFeeIncludes': 'VARCHAR(5)',
194 'MFR_BonusYN': 'VARCHAR(5)',
195 'MFR_BuilderLicenseNumber': 'VARCHAR(5)',
196 'BuilderModel': 'VARCHAR(5)',
197 'BuilderName': 'VARCHAR(5)',
198 'CommunityFeatures': 'VARCHAR(5)',
199 'MFR_CondoFees': 'VARCHAR(5)',
200 'MFR_CondoFeesTerm': 'VARCHAR(5)',
201 'MFR_ZoningCompatibleYN': 'VARCHAR(5)',
202 'ListAgentURL': 'VARCHAR(5)',
203 'ListOfficeURL': 'VARCHAR(5)',
204 'MFR_MaxPetWeight': 'VARCHAR(5)',
205 'MFR_MonthlyCondoFeeAmount': 'VARCHAR(5)',
206 'MFR_NumberOfPets': 'VARCHAR(5)',
207 'MFR_PetRestrictions': 'VARCHAR(5)',
208 'MFR_PetSize': 'VARCHAR(5)',
209 'MFR_ProjectedCompletionDate': 'VARCHAR(5)',
210 'MFR_NumOfOwnYearsPriorToLse': 'VARCHAR(5)',
211 'PropertyAttachedYN': 'VARCHAR(5)',
212 'PropertyCondition': 'VARCHAR(5)',
213 'MFR_PermitNumber': 'VARCHAR(5)',
214 'MFR_BuildingNameNumber': 'VARCHAR(5)',
215 'StreetDirPrefix': 'VARCHAR(5)',
216 'MFR_AmenitiesAdditionalFees': 'VARCHAR(5)',
217 'MFR_ApprovalProcess': 'VARCHAR(5)',
218 'AssociationAmenities': 'VARCHAR(5)',
219 'FireplaceFeatures': 'VARCHAR(5)',
220 'LaundryFeatures': 'VARCHAR(5)',
221 'MFR_AssociationEmail': 'VARCHAR(5)',
222 'MFR_Association2YN': 'VARCHAR(5)',
223 'CoListAgentDirectPhone': 'VARCHAR(5)',
224 'CoListAgentFullName': 'VARCHAR(5)',
225 'CoListAgentKey': 'VARCHAR(5)',
226 'CoListAgentMlsId': 'VARCHAR(5)',
227 'CoListOfficeKey': 'VARCHAR(5)',
228 'CoListOfficeMlsId': 'VARCHAR(5)',
229 'CoListOfficeName': 'VARCHAR(5)',
230 'CarportSpaces': 'VARCHAR(5)',
231 'ElementarySchool': 'VARCHAR(5)',
232 'HighSchool': 'VARCHAR(5)',
233 'MiddleOrJuniorSchool': 'VARCHAR(5)',
234 'MFR_AdditionalRooms': 'VARCHAR(5)',
235 'MFR_AlternateKeyFolioNum': 'VARCHAR(5)',
236 'ArchitecturalStyle': 'VARCHAR(5)',
237 'Disclosures': 'VARCHAR(5)',
238 'MFR_FutureLandUse': 'VARCHAR(5)',
239 'MFR_GarageDimensions': 'VARCHAR(5)',
240 'HomeWarrantyYN': 'VARCHAR(5)',
241 'LotFeatures': 'VARCHAR(5)',
242 'SecurityFeatures': 'VARCHAR(5)',
243 'Vegetation': 'VARCHAR(5)',
244 'View': 'VARCHAR(5)',
245 'MFR_WaterAccess': 'VARCHAR(5)',
246 'WaterBodyName': 'VARCHAR(5)',
247 'MFR_WaterExtras': 'VARCHAR(5)',
248 'MFR_WaterFrontageFeetLake': 'VARCHAR(5)',
249 'WaterfrontFeatures': 'VARCHAR(5)',
250 'MFR_WaterView': 'VARCHAR(5)',
251 'WindowFeatures': 'VARCHAR(5)',
252 'MFR_SubdivisionNum': 'VARCHAR(5)',
253 'MFR_PreviousStatus': 'VARCHAR(5)',
254 'MFR_AdditionalWaterInformation': 'VARCHAR(5)',
255 'AccessibilityFeatures': 'VARCHAR(5)',
256 'MFR_BuildingElevatorYN': 'VARCHAR(5)',
257 'Fencing': 'VARCHAR(5)',
258 'MFR_OtherExemptionsYN': 'VARCHAR(5)',
259 'OtherStructures': 'VARCHAR(5)',
260 'MFR_BarnFeatures': 'VARCHAR(5)',
261 'HorseAmenities': 'VARCHAR(5)',
262 'MFR_EscrowAgentFax': 'VARCHAR(5)',
263 'MFR_FloorNumber': 'VARCHAR(5)',
264 'LotSizeDimensions': 'VARCHAR(5)',
265 'MFR_VirtualTourURLUnbranded2': 'VARCHAR(5)',
266 'MFR_CurrencyMonthlyRentAmt': 'VARCHAR(5)',
267 'MFR_DaysNoticeToTenantIfNotRenew': 'VARCHAR(5)',
268 'MFR_EndDateofLease': 'VARCHAR(5)',
269 'MFR_MonthToMonthOrWeeklyYN': 'VARCHAR(5)',
270 'MFR_AuctionPropAccessYN': 'VARCHAR(5)',
271 'GreenEnergyGeneration': 'VARCHAR(5)',
272 'MFR_PropertyDescription': 'VARCHAR(5)',
273 'MFR_WaterFrontageFeetCanalFresh': 'VARCHAR(5)',
274 'UnitNumber': 'VARCHAR(5)',
275 'AdditionalParcelsDescription': 'VARCHAR(5)',
276 'NumberOfLots': 'VARCHAR(5)',
277 'MFR_DisasterMitigation': 'VARCHAR(5)',
278 'OtherEquipment': 'VARCHAR(5)',
279 'MFR_PlannedUnitDevelopmentYN': 'VARCHAR(5)',
280 'MFR_AGExemptionYN': 'VARCHAR(5)',
281 'StreetDirSuffix': 'VARCHAR(5)',
282 'MFR_NumberOfSeptics': 'VARCHAR(5)',
283 'MFR_NumberOfWells': 'VARCHAR(5)',
284 'RoadResponsibility': 'VARCHAR(5)',
285 'Possession': 'VARCHAR(5)', 'AssociationName2': 'VARCHAR(5)',
286 'MFR_CondoLandIncludedYN': 'VARCHAR(5)',
287 'MFR_NumTimesperYear': 'VARCHAR(5)',
288 'MFR_PoolDimensions': 'VARCHAR(5)',
289 'MFR_MontlyMaintAmtAdditionToHOA': 'VARCHAR(5)',
290 'MFR_WaterFrontageFeetPond': 'VARCHAR(5)',
291 'MFR_ComplexCommunityNameNCCB': 'VARCHAR(5)',
292 'VirtualTourURLBranded': 'VARCHAR(5)',
293 'MFR_AttributionContact': 'VARCHAR(5)',
294 'MFR_WaterFrontageFeetCanalSalt': 'VARCHAR(5)',
295 'MFR_WaterFrontageFeetBayHarbor': 'VARCHAR(5)',
296 'AssociationFee2': 'VARCHAR(5)',
297 'AssociationFee2Frequency': 'VARCHAR(5)',
298 'MFR_BonusAmount': 'VARCHAR(5)',
299 'MFR_BonusExpirationDate': 'VARCHAR(5)',
300 'MFR_FarmType': 'VARCHAR(5)',
301 'MFR_Development': 'VARCHAR(5)',
302 'MFR_WaterFrontageFeetRiver': 'VARCHAR(5)',
303 'MFR_Management': 'VARCHAR(5)',
304 'MFR_PublicRemarksAgentSpanish': 'VARCHAR(5)',
305 'AvailabilityDate': 'VARCHAR(5)',
306 'MFR_LongTermYN': 'VARCHAR(5)',
307 'MFR_NumberOfPaddocksPastures': 'VARCHAR(5)',
308 'MFR_OriginatingSystemTimestamp': 'VARCHAR(5)',
309 'MFR_UnitCount': 'VARCHAR(5)', 'Basement': 'VARCHAR(5)',
310 'MFR_WaterFrontageFeetCanalBrackish': 'VARCHAR(5)',
311 'MFR_WaterFrontageFeetGulfOcean': 'VARCHAR(5)',
312 'MFR_WaterFrontageFeetICW': 'VARCHAR(5)',
313 'MFR_OtherFeesAmount': 'VARCHAR(5)',
314 'MFR_OtherFeesTerm': 'VARCHAR(5)',
315 'GreenEnergyEfficient': 'VARCHAR(5)',
316 'AssociationPhone2': 'VARCHAR(5)',
317 'MFR_VirtualTourURLBranded2': 'VARCHAR(5)',
318 'GreenIndoorAirQuality': 'VARCHAR(5)',
319 'MFR_NumberOfStalls': 'VARCHAR(5)',
320 'MFR_BuyersPremium': 'VARCHAR(5)',
321 'BodyType': 'VARCHAR(5)',
322 'MFR_WaterFrontageFeetCreek': 'VARCHAR(5)',
323 'OffMarketDate': 'VARCHAR(5)',
324 'LandLeaseAmount': 'VARCHAR(5)',
325 'MFR_WaterFrontageFeetLakeChain': 'VARCHAR(5)',
326 'RoadFrontageType': 'VARCHAR(5)',
327 'MFR_TotalNumBuildings': 'VARCHAR(5)',
328 'ListTeamName': 'VARCHAR(5)',
329 'MFR_WaterFrontageFeetBeachPrvt': 'VARCHAR(5)',
330 'MFR_WaterFrontageFeetBeachPub': 'VARCHAR(5)',
331 'MFR_WaterFrontageFeetMarina': 'VARCHAR(5)',
332 'MFR_WaterFrontageFeetBayou': 'VARCHAR(5)',
333 'MFR_GreenLandscaping': 'VARCHAR(5)',
334 'ListTeamKey': 'VARCHAR(5)',
335 'ListTeamKeyNumeric': 'VARCHAR(5)',
336 'MFR_PricePerAcre': 'VARCHAR(5)',
337 'MFR_PropertyManager': 'VARCHAR(5)',
338 'MFR_PropertyManagerPhone': 'VARCHAR(5)',
339 'GreenWaterConservation': 'VARCHAR(5)'
340};
341
342
343// MySQL connection configuration
344const connection = mysql.createConnection({
345 host: "localhost",
346 user: "REDACTED",
347 password: "REDACTED",
348 database: "stellardb",
349});
350
351connection.connect((err) => {
352 if (err) {
353 console.error("Error connecting to the database:", err.stack);
354 return;
355 }
356
357 console.log("Connected to the database.");
358
359 // Create 'properties' table
360 const columns = Object.entries(headersAndTypes)
361 .map(([header, type]) => `\`${header}\` ${type}`)
362 .join(", ");
363
364 const createTableQuery = `CREATE TABLE IF NOT EXISTS properties (${columns})`;
365
366 connection.query(createTableQuery, (err) => {
367 if (err) {
368 console.error("Error creating the 'properties' table:", err.stack);
369 return;
370 }
371
372 console.log("Created 'properties' table.");
373
374 // Parse CSV file and insert rows into the 'properties' table
375 fs.createReadStream("data.csv")
376 .pipe(csvParser())
377 .on("data", (row) => {
378 const values = Object.entries(row)
379 .map(([header, value]) => `'${value.replace(/'/g, "''")}'`)
380 .join(", ");
381
382 const insertRowQuery = `INSERT INTO properties (${Object.keys(row)
383 .map((header) => `\`${header}\``)
384 .join(", ")}) VALUES (${values})`;
385
386 connection.query(insertRowQuery, (err) => {
387 if (err) {
388 console.error("Error inserting row into the 'properties' table:", err.stack);
389 }
390 });
391 })
392 .on("end", () => {
393 console.log("Finished inserting rows from data.csv into the 'properties' table.");
394 connection.end();
395 });
396 });
397});
398